1 Reply Latest reply: May 17, 2018 12:26 PM by Gysbert Wassenaar RSS

    Only show two years

    Wanyun Yang

      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!

        • Re: Only show two years
          Gysbert Wassenaar

          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.