Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dena_reavis
Employee
Employee

QlikView Variable Troubleshooting, trying to improve user experience

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  

namevar NameLet resultIs it expanding to this?Or this?
Salesvar1Sum(Sales)25  
Avg Pricevar2$(var1)/$(var3)6.25Sum(Sales)/Sum(Qty)25/4
Quantityvar3Sum(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

Labels (2)
3 Solutions

Accepted Solutions
Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.

View solution in original post

marcus_sommer

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

View solution in original post

dena_reavis
Employee
Employee
Author

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. 

View solution in original post

3 Replies
Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
marcus_sommer

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
Employee
Employee
Author

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.