Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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.