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: 105

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: 371

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: 105

Thanks a lot, this worked.
SO  
#4 Posted : Thursday, September 7, 2017 10:39:39 AM(UTC)
SO
Rank: Advanced Member

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

I'm looking into an alternative version of this with a smaller selection of critical data.

My crosstab has 5 levels and was setup with calculations as you suggested. (1 status calculation, 5 calculations to count status 1, 5 calculations to count total rows and 1 calculation to get the % status1/total rows).

However, now I have made a month comparison with 12 groups (-01, -02, -03....-12) to group the previous 12 completed months. For readability and user friendliness I would like the comparison to be horisontal.

Is there a good way to solve this with horisontal comparison?

With vertical comparison my previous calculation works fine, but the readability and user friendliness is not good since I get 12 repetitions of the dimensions, 1 for each comparison group.

I tried to set up a Status calculation for the "-12" group in the comparison, this was done with formula "if sum(d-1, 0, m1) >= -5 then 1 else 0"
Why does it says status "1) for all comparison groups from -01 to -11?
UserPostedImage

Do I need to make 12 status columns, 12x5 count "status 1" columns and 12x5 count "total rows" columns, and 1 "%" column? That will be like 133 columns.
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