Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

The way to achieve your own success is the willingness to help somebody else. Go for it!

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: QlikView Variable Troubleshooting, trying to i...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

dena_reavis

Employee

2020-05-04
03:51 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

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

654 Views

3 Solutions

Accepted Solutions

Brett_Bleess

Former Employee

2020-05-27
08:14 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

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.

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

MVP & Luminary

2020-05-27
09:31 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2020-05-27
10:32 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

3 Replies

Brett_Bleess

Former Employee

2020-05-27
08:14 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

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.

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

MVP & Luminary

2020-05-27
09:31 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2020-05-27
10:32 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content