1 Reply Latest reply: Nov 16, 2017 11:45 AM by Sunny Talwar RSS

    How to subset a Pivot Table ?

    Riadh Azouzi

      Hi all, 

       

      I have an item list per department along the different ordered quantities and price per item over different periods.
      I need to compute the inflation rate between two periods.
      The formula : Qty_N(Price_N - Price_N-1) / (Qty_N*Price_N-1)  
      i can show it with two pivot table :

      1)

      DepartmentItemQty ordered 2016Qty ordered 2017Price 2017Inflation rate
      24A0657-
      24B0123230-
      24C1608-
      24D510891350.747

       

      Dimensions: Department , Item [Light Blue]
      Expressions :
           -Qty : sum(<year=2016>qty)
           -Price : avg(<year=2017> price)
           -Inflation: as formula

       

      2)

      DepartmentItemYearQty ordered Price Inflation rate
      24A2017657-
      24C2016168-
      24D201651030-
      24D2017891350.747

       

      Dimensions: Department , Item, Year

      Expressions :

           -Qty : sum(qty)

           -Price : avg(price)

           -Inflation: as formula but using above function : { col(1) * [col(2)-above(col(2))] } / { col(1) * above(col(2))}

                         

       

      However, I want to compute the average rate of inflation per department
      If I just remove the dimension Item, Qlik will simply add all quantities per year, regardless of their presence in succesive years or not ==> Wrong result.

      Thus I would like to know how can I subset one of the pivot table to show only the right row.

       

      Data Source:
      Qvd file of all retail transactions regarding orders: which item ordered to supplier plus quantity,price,date etc...

       


      Thanks a lot for your attention.