topic is nested if allowed in Average()? in QlikView

is nested if allowed in Average()?

Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: is nested if allowed in Average()?

Gysbert_Wassenaar — Tue, 23 Aug 2016 12:10:04 GMT

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)

Re: is nested if allowed in Average()?

marcus_sommer — Tue, 23 Aug 2016 12:12:06 GMT

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

Re: is nested if allowed in Average()?

Tue, 23 Aug 2016 13:52:02 GMT

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

Re: is nested if allowed in Average()?

marcus_sommer — Tue, 23 Aug 2016 15:21:41 GMT

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

Re: is nested if allowed in Average()?

vinieme12 — Wed, 24 Aug 2016 08:28:14 GMT

have you tried

Aggr (

IF's

, ChartDimension)

preferably use Pick Match as Marcus has suggested

Re: is nested if allowed in Average()?

Wed, 24 Aug 2016 08:41:44 GMT

Thanks for this