Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

wanyunyang
Contributor

Only show two years

In my qlik sense, I want to put my Year as a pivot table column and a filter, which has 2012 to 2018. But I only want to show two latest years in pivot table based on the filter. For example, if I select Year=2017 in the filter, the pivot table Year column will show 2016 and 2017. Is it possible to do so?

Any advice help. Thanks in advance!

1 Solution

Accepted Solutions

Re: Only show two years

It's possible by turning the measures in the pivot table into set analysis expressions. Change each measure to include {<Year={$(=Max(Year)),$(=Max(Year)-1)}>}. For example sum(Sales) becomes sum({<Year={$(=Max(Year)),$(=Max(Year)-1)}>} Sales). The extra bit overrules the selection and instead selects the maximum possible value, i.e. the value the user selected, and the year before that.


talk is cheap, supply exceeds demand
1 Reply

Re: Only show two years

It's possible by turning the measures in the pivot table into set analysis expressions. Change each measure to include {<Year={$(=Max(Year)),$(=Max(Year)-1)}>}. For example sum(Sales) becomes sum({<Year={$(=Max(Year)),$(=Max(Year)-1)}>} Sales). The extra bit overrules the selection and instead selects the maximum possible value, i.e. the value the user selected, and the year before that.


talk is cheap, supply exceeds demand
Community Browser