topic Re: Pivot table to aggregate text categories across multiple measures in QlikView

Pivot table to aggregate text categories across multiple measures

JohnBaxter — Fri, 18 Jan 2019 16:59:58 GMT

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!

Re: Pivot table to aggregate text categories across multiple measures

sunny_talwar — Fri, 18 Jan 2019 17:10:49 GMT

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

Re: Pivot table to aggregate text categories across multiple measures

JohnBaxter — Fri, 18 Jan 2019 20:05:31 GMT

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