en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
SO  
#1 Posted : Wednesday, August 27, 2014 11:48:39 AM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/19/2014(UTC)
Posts: 72

I have two columns in my crosstab and I need to do a calculation which involves the two columns and I want to show only the total of the calculated column, not the individual rows.

Column names A and B.

Calculated column = ((∑ Column B) - (∑ Column B where value in column A = 0)) / (∑ Column A *1000). And I want to show this in the total row at the top of my crosstab next to my other totals.

Is this possible?
Ole  
#2 Posted : Wednesday, August 27, 2014 12:07:38 PM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 320

The following is based on the assumption that your two columns are made up from two dimension members or from two comparison elements. If they are instead two different measures, the formulas will need to be modified.

Add a calculated column:
sum(d2, 0, m1) * if sum(d1, 0, m1) <> 0 then 1 else 0

Add another calculated column:
sum(c1, all(c), m1) / sum(d1, all, m1) * 1000

The last calculated column should produce the desired result in the Total row and zeros in the other rows.

BR / Ole

Edited by user Wednesday, August 27, 2014 12:08:36 PM(UTC)  | Reason: Not specified

I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
SO  
#3 Posted : Wednesday, August 27, 2014 1:51:42 PM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/19/2014(UTC)
Posts: 72

od@targit.com wrote:
The following is based on the assumption that your two columns are made up from two dimension members or from two comparison elements. If they are instead two different measures, the formulas will need to be modified.

Add a calculated column:
sum(d2, 0, m1) * if sum(d1, 0, m1) <> 0 then 1 else 0

Add another calculated column:
sum(c1, all(c), m1) / sum(d1, all, m1) * 1000

The last calculated column should produce the desired result in the Total row and zeros in the other rows.

BR / Ole


Thanks. I think I've done something wrong when modifying the code you supplied. My column references are these;

Column B : (d-1,0,m1)
Column A: (d-1,0,m2)
Ole  
#4 Posted : Wednesday, August 27, 2014 1:57:24 PM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 320

Ok. So your two columns are in fact two different measures.

In that case the formulas should be modified to:

Add a calculated column:
sum(d1, 0, m1) * if sum(d1, 0, m2) <> 0 then 1 else 0

Add another calculated column:
sum(c1, all(c), m2) / sum(d1, all, m2) * 1000

BR / Ole

Edited by user Wednesday, August 27, 2014 2:00:05 PM(UTC)  | Reason: Not specified

I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
SO  
#5 Posted : Wednesday, August 27, 2014 2:02:43 PM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/19/2014(UTC)
Posts: 72

od@targit.com wrote:
Ok. So your two columns are in fact two different measures.

In that case the formulas should be modified to:

Add a calculated column:
sum(d1, 0, m2) * if sum(d1, 0, m1) <> 0 then 1 else 0

Add another calculated column:
sum(c1, all(c), m1) / sum(d1, all, m1) * 1000

BR / Ole


The first calculated column, with this code applied, becomes just a replica of my column B. Is that the intention? If I understand your code correctly the first calculated column should have values 1 or 0?
Ole  
#6 Posted : Wednesday, August 27, 2014 2:56:15 PM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 320

The first calculated column should replicate B only in rows where A is a value different from zero. In rows where A is zero, the first calculated column should also show zero.

If possible, please attach a screenshot of your table.
I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
SO  
#7 Posted : Wednesday, August 27, 2014 3:16:01 PM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/19/2014(UTC)
Posts: 72

od@targit.com wrote:
The first calculated column should replicate B only in rows where A is a value different from zero. In rows where A is zero, the first calculated column should also show zero.

If possible, please attach a screenshot of your table.


Thanks, I figured it out now.

#1
if sum(d-1, 0, m2) > 0 then sum(d-1, 0, m1) else 0

#2
sum(c1, all(c), m1) / (sum(d1, all, m2) * 1000)
Ole  
#8 Posted : Wednesday, August 27, 2014 3:17:42 PM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 320

Good. Glad to hear. :-)
I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
Users browsing this topic
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error