Solved: Performance Issues - Pitch - Qlik Community

Report Inappropriate Content · ‎2017-01-16

Hello everyone,

we are trying to compete against another Software with our Qlik knowledge and asking ourselves how Qlik works internally.

What happens if I am using a set analysis like sum({<Week={'1'}>} Value)? Is it like a part aggregation of the flat table or more a calculation on the whole table?

Which indexes are build on the data table if there are any?

Our data table has a few values, dimensions and especially dates.

Thank you in advance,

tuffelchen

marcus_sommer · ‎2017-01-16

The set analysis is not part of the aggregation - it's more a kind of two-step calculation in which the set analysis will be applied like a sql where-clause against the dataset - returning true or false - and only on the remaining records will be the aggregation calculated.

But it will be depend on various factors if this type of expression is the fasted one within your datamodel. Changes within the datamodel regarding to a star-scheme, snowflake-scheme or a big flat-table could make a big difference to the response-time form the UI - for example it could be useful to create flags for your most used and most heavier aggregations, like: sum(Value * Week1Flag). In each case you need to avoid string-comparings like '1' and use instead pure numeric values.

- Marcus

View solution in original post

annafuksa1 · ‎2017-01-16

in this case you will have sum of values only for week 1 (even if select any other week) and for current selection (click clear to clean it if you do not wish it)

Anna

Report Inappropriate Content · ‎2017-01-16

It's the question about the result but what is calculated and especially how it is calculated.

annafuksa1 · ‎2017-01-16

the aggregation happens in the table on your dimension level.

Indexes are being build when reloading the script.

Report Inappropriate Content · ‎2017-01-16

so the sql statement 'select sum(Value) from table group by Week where Week=1' or 'select sum(Value) from table where Week=1' is calculated?

annafuksa1 · ‎2017-01-16

if you will add Week as dimension then option 1 if no dimension option 2

annafuksa1 · ‎2017-01-16

option 1 will be exactly

'select Week, sum(Value) from table group by Week where Week=1'

marcus_sommer · ‎2017-01-16

The set analysis is not part of the aggregation - it's more a kind of two-step calculation in which the set analysis will be applied like a sql where-clause against the dataset - returning true or false - and only on the remaining records will be the aggregation calculated.

But it will be depend on various factors if this type of expression is the fasted one within your datamodel. Changes within the datamodel regarding to a star-scheme, snowflake-scheme or a big flat-table could make a big difference to the response-time form the UI - for example it could be useful to create flags for your most used and most heavier aggregations, like: sum(Value * Week1Flag). In each case you need to avoid string-comparings like '1' and use instead pure numeric values.

- Marcus