en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
SO  
#1 Posted : Wednesday, April 26, 2017 1:40:27 PM(UTC)
SO
Rank: Advanced Member

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

I have a calculated column sum(c1,0,m1) called “status” in my crosstab. This status column has two values, 0 or 1.

I need to make four new calculated columns:
1. Count how many has status 1 and show it in the grand total field
2. Count how many has status 0 and show it in the grand total field
3. Count total number of status rows and show it in the grand total field
4. Calculate how many percent has status 1 of the total status rows and show it in the grand total field.

Any tips how I can do it?

UserPostedImage

Per now I have only selected one vendor at the time, that will be my main use. But if possible it would be nice if I could get it to work when selecting multiple vendors and get both grand total for selected vendors and sub totals for each vendor.

Edited by user Thursday, April 27, 2017 6:33:51 AM(UTC)  | Reason: Not specified

Ole  
#2 Posted : Tuesday, May 2, 2017 7:34:22 AM(UTC)
Ole
Rank: Administration

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

The solution for this will require a number of intermediate calculations. The more dimensions or dimension levels you have on your vertical axis, the more intermediate calculations you will need.

First, a screenshot to show you it can be done:

UserPostedImage

The corresponding calculations:

1: if sum(d1, 0, m1) > 500 then 1 else 0
2: if allcount(d1, all(c), m1) = 0 then sum(c1, 0, m1) else sum(c1, all(c), m1)
3: if allcount(d1, all(c), m1) = 0 then sum(c2, 0, m1) else sum(c2, all(c), m1)
4: if allcount(d1, all(c), m1) = 0 then 1 else allcount(d1, all(c), m1)
5: if allcount(d1, all(c), m1) = 0 then sum(c4, 0, m1) else sum(c4, all(c), m1)
6: sum(c3, 0, m1) / sum(c5, 0, m1) * 100


Notice that 2 and 3 are almost identical, and likewise for 4 and 5. This is necessary for a two-level dimension as in my example. If you have a three-level dimension you would need to have an extra set of almost identical calculations etc. This is in order to aggregate the correct grand totals.

Of course, these intermediate calculations should probably be hidden afterwards - in my example hide calculations 2 and 4.

BR / Ole
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, May 10, 2017 1:52:42 PM(UTC)
SO
Rank: Advanced Member

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

Thanks a lot, this worked.
Users browsing this topic
Anonymous
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