1 Reply Latest reply: Dec 7, 2017 4:49 AM by omar bensalem RSS

    Calculate Difference in Rows in Pivot

    Radu Panciuc

      Hello,

       

      I have a pivot table in Qlik Sense for which I want to see how the measures changed from one year to another.

       

      In the rows I have customer name and the year (2016&2017) and in the measures i have several sums or other calculations. I would like to see the difference between years as well. The table looks something like this:

       

          

      CustomerYearSalesCost
      Customer A201610060
      201715080
      Difference5020

       

      What is the best way to do this?

       

      Thanks,

      Radu

        • Re: Calculate Difference in Rows in Pivot
          omar bensalem

          SUPPOSE U HAVE THIS:

           

          tab:

          load * Inline [

          Customer, Year, Sales, Cost

          CustomerA,  2016, 100,60

          CustomerA,  2017, 150,80

          CustomerB,  2016, 180,90

          CustomerB,  2017, 150,80

          ];

           

          Create a pivot table:

          as dimension: Customer

          Add line : Year

           

          as measure1:

           

          if(Dimensionality()=1, 'Difference between: '&max(Year)&' and '&(max(Year)-1)&' for '&Customer &' is ' &(sum({<Year={"$(=max(Year))"}>}Sales)-sum({<Year={"$(=max(Year)-1)"}>}Sales)),

          Sum(Sales))


          color it by expression :

          if(Dimensionality()<>1,White(), if(Dimensionality()=1  and (sum({<Year={"$(=max(Year))"}>}Sales)-sum({<Year={"$(=max(Year)-1)"}>}Sales)) >0,

          Green(), red()))



           

          as Measure2:

          if(Dimensionality()=1, 'Difference between: '&max(Year)&' and '&(max(Year)-1)&' for '&Customer &' is ' &(sum({<Year={"$(=max(Year))"}>}Cost)-sum({<Year={"$(=max(Year)-1)"}>}Cost)),

          Sum(Cost))


          Color it by expression :

          if(Dimensionality()<>1,White(), if(Dimensionality()=1  and (sum({<Year={"$(=max(Year))"}>}Cost)-sum({<Year={"$(=max(Year)-1)"}>}Cost)) >0,

          Green(), red()))


          result:

          Capture.PNG