Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JohnBaxter
Contributor
Contributor

Pivot table to aggregate text categories across multiple measures

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 RefMigration WaveDesign StatusBuild StatusProduction Readiness
ABCP1 10At RiskNeeds AttentionOK
DEFP1 10OKOKOK
GHIP2 11High RiskHigh RiskAt Risk
JKLP1 9Needs AttentionOKOK
MNOP2 11At RiskNeeds AttentionNeeds Attention

 

I'd like the resulting pivot table to look like this:

 

  Design Status Build Status Production Readiness
Migration Wave Total%Total%Total%
P1 9OK  1100%1100%
 Needs Attention1100%    
 At Risk      
 High Risk      
P1 10OK150%150%2100%
 Needs Attention  150%  
 At Risk150%    
 High Risk      
P2 11OK      
 Needs Attention  150%150%
 At Risk150%  150%
 High Risk150%150%  

Thanks in advance!

1 Solution

Accepted Solutions
sunny_talwar

Something like this

image.png

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))

View solution in original post

2 Replies
sunny_talwar

Something like this

image.png

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))
JohnBaxter
Contributor
Contributor
Author

Sunny, can't thank you enough for the solution and quick response!!!! This has worked brilliantly!!