
Re: Qlik Sense Calculation with Missing Data
Sunny Talwar Jul 14, 2017 1:04 PM (in response to John Lewis)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))

Trailing Sales Example.qvf 176.0 K

Re: Qlik Sense Calculation with Missing Data
John Lewis Jul 17, 2017 11:47 AM (in response to Sunny Talwar )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 Jul 17, 2017 11:54 AM (in response to John Lewis)That part is just saying that include only those restaurants where Sum({<Week={201705}>} Sales) is greater than 0. Look here for more explanation
The concept we have used is the concept of using search strings within set analysis

Re: Qlik Sense Calculation with Missing Data
John Lewis Jul 17, 2017 12:34 PM (in response to Sunny Talwar )Thank you!


