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

# Qlik Sense Calculation with Missing Data

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

 Restaurant Sales Trailing 4 Weeks Growth % (Incorrect) Trailing 4 Weeks for Calc Growth % (Correct) 1 500 549.25 -9.0% 549.25 -9.0% 2 0 615.25 N/A 0 N/A 3 546 480.25 13.7% 480.25 13.7% 4 845 698.25 21.0% 698.25 21.0% Total 630.33 585.75 7.6% 575.92 9.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

• ###### Re: Qlik Sense Calculation with Missing Data

Try this

((Sum({<Week={"201705"}>} Sales)/Count({<[Week]={"201705"}>} distinct Restaurant)/Count({<[Week]={"201705"}>} distinct Week))

-

(Sum({<Week=,Week={">=201701 <201705"}, Restaurant = {"=Sum({<Week={201705}>} Sales) > 0"}>} Sales)/Count({<Week=,Week={">=201701 <201705"}, Restaurant = {"=Sum({<Week={201705}>} Sales) > 0"}>} Restaurant)))

/

(Sum({<Week=,Week={">=201701 <201705"}, Restaurant = {"=Sum({<Week={201705}>} Sales) > 0"}>} Sales)

/

Count({<Week=,Week={">=201701 <201705"}, Restaurant = {"=Sum({<Week={201705}>} Sales) > 0"}>} Restaurant))

• ###### Re: Qlik Sense Calculation with Missing Data

Sunny, this resolved the issue - thank you!

I am new to Qlik Sense and set analysis, so I am wondering if you could provide an explanation of the set analysis code change you made.  In doing some research, I understand you incorporated a function (i.e. Restaurant = {"=Sum({<Week={201705}>} Sales) > 0"}) in the set analysis.  However, I don't understand how it works.  When I try to interpret it, I don't get the connection between the summing of Sales for the Week 201705 and the inclusion/exclusion of the Restaurant that meets the criteria.

If you could me understand how this works, that would be very helpful!

John

• ###### Re: Qlik Sense Calculation with Missing Data

That part is just saying that include only those restaurants where Sum({<Week={201705}>} Sales) is greater than 0. Look here for more explanation

A Primer on Set Analysis

The concept we have used is the concept of using search strings within set analysis

• ###### Re: Qlik Sense Calculation with Missing Data

Thank you!