Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
rido1421
Contributor III

Pivot table limiting column expression

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)

JanFebMarKPI Target
Brand1000120011001500

any Idea how I can achive this.

This is what I am currently getting:

JanKPI TargetFebKPI TargetMarKPI Target
Brand100015001200150011001500

Your assistance is appreciated.

Thanks

1 Solution

Accepted Solutions
MVP
MVP

Re: Pivot table limiting column expression

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

3 Replies
MVP
MVP

Re: Pivot table limiting column expression

use KPI Target  expression as Calculated Dimension..

This has to be something like below..

Aggr(YourKPITargetExpression,SomeDimensionField)

something like..


Aggr(SUM(TOTAL Sales),Customer)

MVP
MVP

Re: Pivot table limiting column expression

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

rido1421
Contributor III

Re: Pivot table limiting column expression

Thank you this worked great