4 Replies Latest reply: Jul 17, 2017 12:34 PM by John Lewis RSS

    Qlik Sense Calculation with Missing Data

    John Lewis

      I am trying to create a calculation in a Qlik Sense application, and am not able to properly do the calculation.  The calculation compares current week sales for a restaurant to the prior 4 weeks.  The issue is when there is a restaurant that is missing current week sales, but has sales for the prior 4 weeks.  When a restaurant is missing current week sales, the prior 4 week sales should be ignored, and this works OK at the restaurant level.  However, when calculating totals for a set of restaurants, the same rule applies - the prior 4 week sales for the restaurant missing current week sales should be excluded from the calculation of the totals.

       

      Here is an illustration of the problem.  In the application, I am getting the results as indicated in the Growth % (Incorrect) column in the table below.  However, the calculation should reflect the column Growth % (Correct).

           

      RestaurantSalesTrailing 4 WeeksGrowth %
      (Incorrect)
      Trailing 4 Weeks for CalcGrowth %
      (Correct)
      1500549.25-9.0%549.25-9.0%
      20615.25             N/A0                N/A
      3546480.2513.7%480.2513.7%
      4845698.2521.0%698.2521.0%
      Total630.33585.757.6%575.929.4%

       

      I have also attached the application with example data and the calculations.

       

      Is there a way I can do this calculation correctly as indicated in the above table?

       

      Thanks!

       

      John