6 Replies Latest reply: Jun 14, 2018 4:36 PM by Mohamed Elafifi RSS

    Issue with range calculation in Pivot Table

    Mohamed Elafifi

      Hi Qlik community,

       

      I am having an issue with a range expression that is calculating a three month average. When I have Customer and Year-Month Dimensions as Rows, I get the number I expect to see. However, the business users don't want the default view in this format. They want the Year-Month and the Measures to be in the column, and the customer alone in the Rows. The problem, then, is that the numbers get all wacky when I move the Year-Month dimension to the column.

       

      Attached below are screenshots of both, and here is the calculation that is calculating the 3-Month rolling DPPM:

       

      ((RangeAvg(Above(count({<MonthName, Year, Month>}DISTINCT[iQMS_ID]), 0, 3)) * Avg(1))/

      (RangeAvg(Above(Sum({<MonthName, Year, Month>}[QTY_SHIPPED]), 0, 3)) * Avg(1)))

      *1000000



      NOTE: All other dimensions react perfectly fine when Year-Month is moved from row to column!

       

      Any help with this will be much appreciated!

       

      Mohamed

       

      DPPMNumbersCorrect.png

      DPPMNumbersInorrect.png