Question Regarding Straight Table Recalculations

[pedrohenriqueperna]

Hi,

I've built a big table, using help tables and filters as checkboxes, to dynamically display different columns that holds dynamic complex expressions that might vary according to different buttons and variable values.

The output is great and it's exactly what I wanted, except the table holds 100+ columns (not necessarily simultaneously) and is built on top of million of rows data, which makes it super sluggish.

I know I can use the "add-on" calculation condition to delay or limit it's loading, but I wonder if there are other possibilities to improve performance. Any suggestion will be appreciated.

Furthermore, how do recalculations work for each column? I've noticed that even with the calculation add-on surpressed, columns seems to recalculate on most changes in other columns and filters, is that so?

Considering that most columns depends on a checkbox selection to be displayed, if I add the same check condition (show column if) before the column main expression, will it recalculate only if the checkbox is marked?

[marcus_sommer]

If a dimension/expression is disabled (directly or per condition) within the dimension/expression tab it's not calculated. If the presentation tab is used the column is just hidden and will be calculated and could be further used in references.

Be aware that the data-model has a huge impact on the UI performance. Recommended is the use of a star-scheme which covers all essential logic. This means all possible matching/pre-calculation/formatting should be done within the script and not in the UI. Avoiding or at least minimizing the need of if-loops, aggr-constructs and interrecord-functions are mandatory to keep the UI responsive by larger data-sets.

Further controlling the table with buttons/variables may not the best choice in regard to the performance because their evaluation may trigger re-calculations and/or preventing the caching. In the most scenarios are direct selection per list-box(es) simpler, better readable and more performant.

[PrashantSangle]

Instead of calculated condition for hide/show. I suggest to use Qlik Straight table Native functionality > Chart exploration.

Straight Table > Appearance > Chart Exploration > enable.

This work very smooth. I tried with 25 dim & measures. Performance is really good.

[pedrohenriqueperna]

Hi,

It's a good functionality indeed, but unfortunately it doesn't suit in this scenario. Thanks for the tip anyway!

[pedrohenriqueperna]

Hi, Marcus

What do you mean by "if the presentation tab is used the column is just hidden and will be calculated and could be further used in references"?

I think my model is well optimized, with concatenated facts and star-schemed dimensions, like you said, but I'm actually using the aggr() unnecessarily. The request was to build a table with our stores as columns, and each store might display up to 8 different columns (stock, revenue, etc.). I tried using the pivot table to auto plot the stores as columns, but I didn't much like the design and it's harder to customize it using css, so I went for the straight table.

Given the fact that the expressions are much like the same for each store, I've built a UDF to be able to pass parameters and make the code easier to maintain and read. The problem, I guess, is that I'm overusing aggr() within the UDF, which I turns out to be useless after I discovered that I can actually use outer set analysis on a master measure. I'll change everything up and see if it helps. Thanks for the suggestions!

As for the if-loops, I added an if before every column measure to check if the store is selected within the checkbox filter, suspecting it would avoid the expression to be calculated whenever the column is already hidden (as the same condition is used in the "show column if"). Is this a bad or good idea?

(filters by the right, "escopo" being the "stores" that condition the displaying)

[marcus_sommer]

My suggestions to dimension/expression/presentation tabs are related to QlikView but I assume that Sense has quite similar features and also that their behaviour didn't changed.

From a performance point of view are aggr() the worst case and should be really the last option if no other way is possible. Further these if-loops aren't necessary - either the selection state itself controlled which stores belonged to it or if any adjustments to it are wanted the conditions should be applied within the expressions with an appropriate set analysis statement.

It's not quite clear how your table is look like because having a store + a few upper/side dimensions and max. 8 KPI's resulting not in a 100+ column table. Some more details would be helpful.