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 %
      Trailing 4 Weeks for CalcGrowth %
      20615.25             N/A0                N/A


      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?