Help with using a lot of expressions inside a Pick()?

[EderMachado1]

Good evening, QlikView gurus!

So, were currently having this issue: We need to develop an Income Statement for our financial deparment, for a very specific document which has a massive amount of data. Due to the sources coming from many different tables and the income had to come with very specific rows we had to work with a self made dimension. In an old version of it we were using a Pick(Match()) statement with a built dimension to match each single line of the Statement to a single expression 54 different times, so 54 different expressions. And this was only for a single column of the statement for the current selected month, we also have to make another column for a YTD analysis and a third column for month selected with a specific concept selected in the set analysis plus 2 extra columns for percentile comparisons.

As I'm sure you already noticed, the amount of calculations that have to be done is massive, thus the chart won't even load at all when checked at the Access Point. Out user needs to only filter month and year, and last time we made a test it took the chart around 15 minutes to show any kind of information (from the month of September mind you, which doesn't have as much information as any other month). 

I'm tasked with the monumental job of making the darned thing work and make it show information within a reasonable timespan after the user selected their filters (30 seconds at most). 

This is a small snippet of how it must look like (this was taken after a debug load of only 10000 rows so ignore the numbers shown). 

And this is how the main expression looks like. The thing is, I'm currently calculating 54*5 expressions at the same time with a massive data volume which cannot be reduced anymore, so I don't fault the system for not being able to show info in a timely manner, but I know I can optimize it somehow. 

First, the Pick(Match()) load was removed by loading the dimensions via an excel sheet, thus removing the need to use match and only having to use Pick(). Then, as you can see in certain lines I'm not writting a hard expression but I0m calling a different expression/column. For expressions which are recurrent through the entire Pick() like totals which have to be used multiple times I received some advice which told me I should make a new expression for each one of those repeated expressions and call the name of the column instead, so the system only has to calculate the expression once instead of as many times as I call it:

Like this, the expression called "c_Otros" is completely hidden but otherwise enabled, and it gets called multiple times in the main expression, like 4 or 5 so in theory it saves loading time. But I have my doubts: Is this really a thing? The expression will really only be calculated once and use the total when the name of the column gets called instead of the fully written expression? Or is it just wishful advice which tries fruitlessly to cut loading times? I would argue it would hinder us instead since calculating something although hidden will still consume resources anyway (and I have like 20 of those hidden calculations), wouldn't it? and It really only calculates it once or that's not how it works? 

Other than this, I'm completely stumped. I really don't want to mess with the script or data volume in any way since I0m not the only one using the document and we have many many many more reports and charts in different tabs so messing with data will probably bring more issues. I would apreciate any kind of advice I would get about how to improve the performance of the chart. Oh, and particularly I had thought about making 5 different charts instead of a single one and hiding them cleverly using layers on top of each other so it looks like a single one, but our user heavily utilizes the send to excel option so he would need all 5 charts sent to the same excel sheet, so the result has to be in a single chart unless something funny can be done ussing triggers and buttons.

Is the sheer amount of expressions a problem no matter how I slice it? Can a different function be used instead of Pick() or Pick(Match())? Can this even be done without messing with the script?

I can provide more information or screenshots if needed. Thank you for your help, and have a great day!

[marcus_sommer · Accepted solution]

I think there are no many possibilities to increase the chart-performance in your case and therefore nothing what would reduce the response-times below 30 seconds. I wouldn't try it - at least not with the underlying datamodel.

The reason for your observed slowness is that Qlik for your 54 * 5 Expressions needs to create 54 * 5 virtual tables to create the dimensionality respectively the context on which the expressions itself could be performed. Although most of the calculations in Qlik are multi-threaded - the creation of these virtual tables are only single-threaded. Especially if the within the expression used fields come not from a single table else multiple ones which may even rather poorly linked (snowflake-scheme or any link-tables approaches) it could take ages to calculate them. Even with rather small datasets you could slowdown a big server quite significantely with such an approach (quite as well as if you also use nested if-loops, aggr-constructs or any kind of interrecord-functions).

Therefore my suggestion is probably no surprise - take a carefully review of the datamodel to develop it within the direction of a star-scheme and to transform as much logic as possible within the script. This may mean to transform your multi-field KPI's into a single-field KPI (I don't know if the following link is directly applicable for it but the main-idea behind it should be definitely helpful why this kind of transforming is usually beneficial: The-Crosstable-Load). A further way in this sense is to create appropriate flags or even dimension-values for your current month, YTD and so on so that you could switch it to a horizontal dimension within a pivot.

Ideally you would end with two native dimensions and a simple single expression like sum(value) ...

- Marcus

[EderMachado1]

Thank you for taking the time to answer, Marcus! But yeah, I definitely agree with you, I started brainstorming a bit with my coworkers and tackling the issue on the script using a crosstable seems to be the best alternative, now let's hope the data volume will not be a problem for the crosstable but hey! That's a different tale altogether.

Thanks again!