QlikView Variable Troubleshooting, trying to improve user experience

[dena_reavis]

I have a few questions that will help me troubleshoot an app with lots of variables. 

I'm hoping to improve the user experience in the app... 

I think how the variables are referenced may be part of the problem. When a variable is defined as let var1 =Sum(Sales) and then use that var1 elsewhere, I don't want it to calculate Sum(Sales) again... how do I use var1 in another expression without recalculating the Sum(Sales)? 

Example  

name	var Name	Let	result	Is it expanding to this?	Or this?
Sales	var1	Sum(Sales)	25
Avg Price	var2	$(var1)/$(var3)	6.25	Sum(Sales)/Sum(Qty)	25/4
Quantity	var3	Sum(Qty)	4

None of the variables are defined with = initially

I tried a few as $(=var1)/$(=var3) and they work great but they do not appear to calculate any faster

This is relatively small app, star schema, fact has about 80 fields and 170,000 rows of data. However, I have 300 variables, of which 100 are using only actual data like Sum(Sales) and the other 200 are calculations using those 100 variables. Relatively simple stuff,  just a whole bunch of them. I must have done something terrible to it because it takes about 30 seconds to calculate all of them. And the object recalculates every time they click on anything. 

I know this is my ignorance and exhaustion, not Qlik, I just need help figuring out what I did wrong. Thanks in advance - DR

[Brett_Bleess · Accepted solution]

About the best I can offer are the following Help links:

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/LoadData/best-p...

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/application-per...

Need to be very careful using variables inside variables, as you have no means of knowing the order in which things are going to calculate, so you may get inconsistent responses in these cases, just FYI.

Regards,
Brett

[marcus_sommer · Accepted solution]

Over the expression-label or column(ExpressionIndex) you could reference to another calculation within the same object. Both methods are not a performance-booster because it makes no sense to duplicate any expression there and if you used them as part within another expression they will be recalculated again because only absolutely identically expressions will be cached. Therefore it's more some kind of convenience.

I think your general approach to use variables for nearly all calculations is the real issue. Variables have the aim to simplify things, for examples to avoid redundance by longer or more complex text/expressions - using them for all expressions especially so simple ones like sum/max/avg(FIELD) is rather the opposite.

I had had also the experience that objects are permanently recalculated even by unrelated actions. I never tried to find the real reason - it may be a feature/bug that if the number of variables (within an object and/or sheet and/or application) hit a certain threshold that it happens but more likely to me is that there is any kind of circular reference between all those variables and which then triggers the recalculation.

- Marcus

[dena_reavis · Accepted solution]

Here's an update... since I posted this, I made the decision to move all 260 calculations to the load script. I had originally used the variables read in from a spreadsheet for transparency and tracking changes to the calculations as this was developed. I do appreciate the suggestions.