Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi qlik folks,
I am given a line chart created by nested if statements as below. I need to create a reference line of average in the chart but I get
'Allocated Memory Exceeded' error message.
Is there a way I can create the average line using nested if statements at all? (Note that I am not allowed to change the expression of the chart).
Many thanks,
For neseted aggregations you need to wrap them with an aggr-function like:
avg(aggr(
pick(match([Sys KPI Number], 1,2,3,....),
Exp1,
Exp2,
...),
Dim1, Dim2))
whereby you need to specify the dimensions to which the expressions should be related to (normally those dimensions which are included in your chart).
- Marcus
The number of nested if statements is probably causing the problem. Perhaps using the pick function works better:
pick([Sys KPI Number], ...first expression... , ...second expression... , ...etc)
Quite often are nested if-loops a nightmare from a performance and developing/maintaining point of view and shouldn't be used so heavily like in your example. I think you will need to change these expression at least by replacing the if-loop with something like this:
pick(match([Sys KPI Number], 1,2,3,....),
Exp1,
Exp2,
...)
- Marcus
Hi Marcus & Gysbert,
Thanks for your inputs. Your suggestions has significantly simplified the script and I'm happy about it. However, the expression doesn't accept the Average function. I tested it in a textbox which give me this message "Error is expression: Nested aggregation not allowed". Any idea?!
Thanks
For neseted aggregations you need to wrap them with an aggr-function like:
avg(aggr(
pick(match([Sys KPI Number], 1,2,3,....),
Exp1,
Exp2,
...),
Dim1, Dim2))
whereby you need to specify the dimensions to which the expressions should be related to (normally those dimensions which are included in your chart).
- Marcus
have you tried
Aggr (
IF's
, ChartDimension)
preferably use Pick Match as Marcus has suggested
Thanks for this