In my prior work as a developer and in talking with my Qlik peers there is agreement that creating binary flags in the script (to assist in identifying key segments) is best practice. Anytime you can pre-process data in the script, the UI will perform faster and have less work to do.
Less widely understood is what to do in the UI once the flags have been created. How should these flags be used in expressions? Which approach yields the best performance? One valid typical approach uses set analysis in the following manner to sum Sales where the flag is true:
=sum({$<ScriptFlag = {1}>} SalesAmount)
However, this approach still requires the UI to conditionally evaluate a true/false comparison. From a performance standpoint (important with big data) there is an alternate approach I recommend - special thanks to Kris Balow (@KrisBalow) from Grange Insurance for suggesting this:
When leveraging binary flags (0,1) from the script, simple multiplication of the (flag * metric) performs much faster in the application UI. In my testing, both methods appear to return valid results.
This concept may not work with all aggregate functions, but sum, avg, and count are compatible. Just think about the math before you do it. It’s faster than set analysis because there is no conditional evaluation involved. It’s a simple straight calculation across all records.
Recently I saw a 70% improvement in response time in a customer application by making this simple shift. Using set analysis the response time between clicks was 4-6 seconds. Using straight multiplication it dropped to 1-2 seconds. In this case, a pretty big deal for the users who were making 500+ selections a day. As always, performance lifts will depend on the application.
=sum(ScriptFlag * SalesAmount) – performs much faster
Questions:
- What performance lift do you get?
- What limitations do you see with this approach?
Regards,
Kyle
.png "Former Employee"){kind=icon}
