is nested if allowed in Average()?

[#1]

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,

[Gysbert_Wassenaar]

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)

[marcus_sommer]

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

[#4]

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

[marcus_sommer · Accepted solution]

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

[vinieme12]

have you tried

Aggr (

          IF's

               , ChartDimension)

preferably use Pick Match as Marcus has suggested

[#7]

Thanks for this