Qlik Sense Calculation with Missing Data

[Anonymous]

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

[sunny_talwar · Accepted solution]

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

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

Thank you!