topic Qlik Sense Calculation with Missing Data in App Development

Qlik Sense Calculation with Missing Data

Anonymous — Fri, 14 Jul 2017 16:40:19 GMT

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

sunny_talwar — Fri, 14 Jul 2017 17:04:59 GMT

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

Anonymous — Mon, 17 Jul 2017 15:47:49 GMT

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

sunny_talwar — Mon, 17 Jul 2017 15:54:40 GMT

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

Anonymous — Mon, 17 Jul 2017 16:34:02 GMT

Thank you!