topic Re: Pivot table limiting column expression in QlikView

Pivot table limiting column expression

rido1421 — Tue, 19 Jan 2016 08:58:14 GMT

Hi There

I have a pivot table

in my Pivot table I have two expressions

Sum(Actuals)- I would like to see this by month

Sum(KPI) -I only want to see this column once, as this is the KPi for the Year.(This is a very complex expression and I cannot use it as a dimension)

	Jan	Feb	Mar	KPI Target
Brand	1000	1200	1100	1500

any Idea how I can achive this.

This is what I am currently getting:

	Jan	KPI Target	Feb	KPI Target	Mar	KPI Target
Brand	1000	1500	1200	1500	1100	1500

Your assistance is appreciated.

Thanks

Re: Pivot table limiting column expression

MK_QSL — Tue, 19 Jan 2016 09:15:48 GMT

use KPI Target expression as Calculated Dimension..

This has to be something like below..

Aggr(YourKPITargetExpression,SomeDimensionField)

something like..

Aggr(SUM(TOTAL Sales),Customer)

Re: Pivot table limiting column expression

jonathandienst — Tue, 19 Jan 2016 09:28:18 GMT

If you use Dimensionality() -- or SecondaryDimensionality() for rows -- you can create a custom total expression which you could use for your KPI value.

Set up a pivot table with Month as the dimension and Sum(Actuals) as the expression. Enable partial totals for the expressions (on the Presentation tab) and pivot the table so the Months are columns. Now modify the expression:

=If(SecondaryDimensionality() = 0, Sum(KPI), Sum(Actuals))

Finally, change the "Label for Totals" to read "KPI Target" (or whatever you want it to read).

If done correctly, that should result in the monthly amounts and the KPI value in the 'total' column labelled with the label you entered.

Re: Pivot table limiting column expression

rido1421 — Tue, 19 Jan 2016 09:54:33 GMT

Thank you this worked great