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)))


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


      Any help with this will be much appreciated!