Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
JohnBaxter
New 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

Re: Pivot table to aggregate text categories across multiple measures

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

Re: Pivot table to aggregate text categories across multiple measures

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

JohnBaxter
New Contributor

Re: Pivot table to aggregate text categories across multiple measures

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