Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'd like to aggregate the count of text categories across multiples measures in a Pivot Table.
The underlying data looks as follows, the categories are always - OK, Needs Attention, At Risk, High Risk:
Application Ref | Migration Wave | Design Status | Build Status | Production Readiness |
ABC | P1 10 | At Risk | Needs Attention | OK |
DEF | P1 10 | OK | OK | OK |
GHI | P2 11 | High Risk | High Risk | At Risk |
JKL | P1 9 | Needs Attention | OK | OK |
MNO | P2 11 | At Risk | Needs Attention | Needs Attention |
I'd like the resulting pivot table to look like this:
Design Status | Build Status | Production Readiness | |||||
Migration Wave | Total | % | Total | % | Total | % | |
P1 9 | OK | 1 | 100% | 1 | 100% | ||
Needs Attention | 1 | 100% | |||||
At Risk | |||||||
High Risk | |||||||
P1 10 | OK | 1 | 50% | 1 | 50% | 2 | 100% |
Needs Attention | 1 | 50% | |||||
At Risk | 1 | 50% | |||||
High Risk | |||||||
P2 11 | OK | ||||||
Needs Attention | 1 | 50% | 1 | 50% | |||
At Risk | 1 | 50% | 1 | 50% | |||
High Risk | 1 | 50% | 1 | 50% |
Thanks in advance!
Something like this
Load the data using the CrossTable load
Table: CrossTable(Field, Value, 2) LOAD * INLINE [ Application Ref, Migration Wave, Design Status, Build Status, Production Readiness ABC, P1 10, At Risk, Needs Attention, OK DEF, P1 10, OK, OK, OK GHI, P2 11, High Risk, High Risk, At Risk JKL, P1 9, Needs Attention, OK, OK MNO, P2 11, At Risk, Needs Attention, Needs Attention ];
and then use this
Dimensions
1) Migration Wave 2) Value 3) Field
Expressions
1) Count(Value) 2) Count(Value)/Max(TOTAL <[Migration Wave], Value> Aggr(Count(Value), [Migration Wave], Field, Value))
Something like this
Load the data using the CrossTable load
Table: CrossTable(Field, Value, 2) LOAD * INLINE [ Application Ref, Migration Wave, Design Status, Build Status, Production Readiness ABC, P1 10, At Risk, Needs Attention, OK DEF, P1 10, OK, OK, OK GHI, P2 11, High Risk, High Risk, At Risk JKL, P1 9, Needs Attention, OK, OK MNO, P2 11, At Risk, Needs Attention, Needs Attention ];
and then use this
Dimensions
1) Migration Wave 2) Value 3) Field
Expressions
1) Count(Value) 2) Count(Value)/Max(TOTAL <[Migration Wave], Value> Aggr(Count(Value), [Migration Wave], Field, Value))
Sunny, can't thank you enough for the solution and quick response!!!! This has worked brilliantly!!