3 Replies Latest reply: Aug 21, 2013 6:50 AM by Abey Chandy RSS

    Average as subtotal in pivot table

    Torunn Midtsjo

      Hi!

       

      Attached you can see a pivot-table made in QlikView 11. 

       

      The table has 2 dimensions and 3 expressions:

       

      Dimension 1:

      =SM9.CASES_MONTHLY_HISTORY.YEAR &'-'& DATE(MONTHSTART(MAKEDATE(SM9.CASES_MONTHLY_HISTORY.YEAR, SM9.CASES_MONTHLY_HISTORY.MONTH,1)), 'MM')

       

      Dimension 2:

      %SM9.CASES.REGION

       

      Expression1:

      =COUNT({$<SM9.CASES.TYPE={'QUOTE'},SM9.CASES.OCMQM1.LEVERANSE_TILBUD={'LEVERANSEKOORDINERING'},SM9.CASES_MONTHLY_HISTORY.RUB_DELIVERY_STATUS={'Grønn'}>}SM9.CASES_MONTHLY_HISTORY.RUB_DELIVERY_STATUS)
      +
      COUNT({$<SM9.CASES.TYPE={'QUOTE'},SM9.CASES.OCMQM1.LEVERANSE_TILBUD={'LEVERANSEKOORDINERING'},SM9.CASES_MONTHLY_HISTORY.RUB_DELIVERY_STATUS={'Grå'}, %SM9.CASES.OPEN_TIME={'=((NUM(FLOOR(makedate(SM9.CASES_MONTHLY_HISTORY.YEAR,SM9.CASES_MONTHLY_HISTORY.MONTH,1))) - NUM(FLOOR(%SM9.CASES.OPEN_TIME))) < 3)'}>}SM9.CASES_MONTHLY_HISTORY.RUB_DELIVERY_STATUS)

       

      Expression 2:

      =COUNT({$<SM9.CASES.TYPE={'QUOTE'},SM9.CASES.OCMQM1.LEVERANSE_TILBUD={'LEVERANSEKOORDINERING'}>}SM9.CASES_MONTHLY_HISTORY.RUB_DELIVERY_STATUS)

       

      Expression 3:

      =column (1) / column(2)

       

      At the moment the pivot table only includes data for 1 month, but the number of months will increase up to 12 months.  

       

      My problem is that I want the the total for expression "% Grønne" to show a subtotal as an average for each month.  The average for each month shall be calculated as "%Grønne" for each Regions divided by number of regions .  For the current month: (96,7% + 92,3% + 97,6% + 85,0% + 84,8%) / number of regions = 91,3%

       

      I guess the solution is an expression with use of aggr-funtion, but I am not able to fix this expression on my own.  How can I for each month show an average for the month based on the %Grønne for each Region?

       

      The table does not need to show the result for each region.  Users do only need to see the month and the average for the month based on all Regions.

       

      I hope anyone can help me with a solution. 

       

      Best regards

      Torunn