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:



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'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?


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.