Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

is nested if allowed in Average()?

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,

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: is nested if allowed in Average()?

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

View solution in original post

6 Replies
MVP & Luminary
MVP & Luminary

Re: is nested if allowed in Average()?

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)


talk is cheap, supply exceeds demand
MVP & Luminary
MVP & Luminary

Re: is nested if allowed in Average()?

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

Not applicable

Re: is nested if allowed in Average()?

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

MVP & Luminary
MVP & Luminary

Re: is nested if allowed in Average()?

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

View solution in original post

Highlighted
vinieme12
Esteemed Contributor II

Re: is nested if allowed in Average()?

have you tried

Aggr (

          IF's

               , ChartDimension)

preferably use Pick Match as Marcus has suggested

Not applicable

Re: is nested if allowed in Average()?

Thanks for this