Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

john_lewis
New Contributor

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

     

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

1 Solution

Accepted Solutions

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

4 Replies

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

john_lewis
New Contributor

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

Capture.PNG

john_lewis
New Contributor

Re: Qlik Sense Calculation with Missing Data

Thank you!

Community Browser