Input variable related set analysis

[AnkitMadhukar]

Hi Everyone,


I have a Qlik app that uses input box to get variables , and based on user selection the measure is decided.

The measures are aggr() functions on a fairly large dataset. If I use these measures separately all work fine. But when I club them together in pick statement using input variable , the measures fails to execute. (timed out).
If I apply some filter (like for a month) all things work perfectly fine. Seems Qlik is not able to handle my current set analysis and calculations.
Any ideas on how to optimize it

I also tried using inline table with measure ids and using those for selecting measure but no luck

Eg Set Analysis.
if(GetFieldSelections(Measure) = 'Avg Transaction amount per month' , Avg(Aggr(Sum(TransAmount),YearMonth,AccountNumber)),
if(GetFieldSelections(Measure) = 'Avg Transaction amount per week' ,Avg(Aggr(Sum(TransAmount),YearWeek,AccountNumber)),
if(GetFieldSelections(Measure) = 'Avg Transaction amount per day' ,Avg(Aggr(Sum(TransAmount),TransactionDate_,AccountNumber)) ,)))

this wont work unless I filter out the dataset so that its small enough for Qlik to effectively calculate it

but If I give one measure only , it works fine



Thanks
Ankit

[marcus_sommer]

What you are doing isn't a set analysis else nested aggregations which are placed within nested if-loops.

Your approach might be optimizable by avoiding the nested aggregation. In your case it means something like:

sum(value) / count(AnyField)

I don't know if it's directly possible for your requirements and you may also need some changes within the datamodel for it.

More important is to avoid the nested-if-loops. Here you could change it to:

Avg(Aggr(Sum(TransAmount),AccountNumber,
$(=pick(match(Measure, 'Avg Transaction amount per month', '...', '...'), [YearMonth], ..., ...))))

Ideally you creates already appropriate island-tables for it - means not just loading a measure-field else adding to it the appropriate period-field which would lead to:

Avg(Aggr(Sum(TransAmount),AccountNumber, [$(=MeasurePeriod)]))

Beside this also the kind of datamodel is essential for the UI performance - quite good is a star-scheme and all measures and relevant fields are included in the fact-table and bad are link-table scheme with widely spread fields (in regard to the combination of using them within the dimensions and expressions of an object).

- Marcus

[AnkitMadhukar]

Hi @marcus_sommer ,

Thank you for helping me with this!

Before going with nested if approach , I did try pick and match for the same calculations. 
It had the same issue. Qlik was not able to execute it.

Something like this

pick(Parameter , Avg(Aggr(Sum(TransAmount), YearMonth,AccountNumberz)),Avg(Aggr(Sum(TransAmount), YearWeek,AccountNumberz))
,Avg(Aggr(Sum(TransAmount),TransactionDate_,AccountNumberz)))

Parameter is getting value from input control

Does Qlik execute all the calculations mentioned in pick statement before picking out the result ? 
Thanks

[marcus_sommer]

It's mainly the same - just another more readable syntax - and the logic is that all branches are at first calculated and within a second step comes the evaluation which branch returned at first TRUE. This approach is quite different to many other tools and it has benefits as well as disadvantages.

In general you should avoid explicit nested if-loops as well as the implicit ones - at least if there are really many branches and/or the content within is quite expensive and/or you experienced already performance challenges.

Quite often there are better solutions possible - did you try my suggestion from above?

Beside this the UI performance depends essentially on a well build datamodel - any errors and omissions here couldn't be cured with smart solutions in the UI. Therefore I suggest a review of the datamodel and if it's not a star-scheme in which the fact-table includes all relevant measures - and sometimes also those dimensions of the most heaviest calculations - you should consider an appropriate change.

- Marcus