topic Re: nested aggr - aggr within aggr in QlikView

nested aggr - aggr within aggr

Anonymous — Tue, 04 Mar 2014 19:38:35 GMT

Is there any pitfalls using aggr within aggr like that:

avg(total <practice>

aggr(

avg(

aggr(

avg(score)

,survey,section

)

,survey

)

I know of performance implications but I am wondering if anyone else actually did that successfully. I cannot find any examples but I tested it quickly and looks like aggr nesting works fine.

Re: nested aggr - aggr within aggr

Tue, 04 Mar 2014 20:00:36 GMT

If the data is correct ...

For some rolling periods, we can do sth like that:

sum( aggr( rangesum( above( sum(VALUE),0,3) ),TIME_KEY))

As yoi said, performance is not as good as a simple sum() or avr()

Fabrice

Re: nested aggr - aggr within aggr

hic — Sun, 18 May 2014 20:37:02 GMT

This should work. But why do you want to do it? What's the use-case?

One single Aggr means a two-step nested aggregation.

Two nested Aggr's would mean a three-step nested aggregation.

HIC

Re: nested aggr - aggr within aggr

Anonymous — Mon, 19 May 2014 01:24:52 GMT

Hi Henric,

I was surprised it would work but it does work fine!

my use case was exactly that - three level aggregation. I was working with some survey data (questions/answers) and the requirement was to calculate overall organization's score as a mean of means of all survey sections answers (group of questions). Then in turn, means of sections answers are calculated as means of each individual question scores in each section. So it is a mean of means of means. I considered doing this in a script but the requirement was to calculate the final score on the fly and respect selections.

I did not have a lot of data (a couple million rows with each survey's answer score) and the performance was actually very good - granted we have quite a powerful server.

Re: nested aggr - aggr within aggr

hic — Mon, 19 May 2014 05:51:14 GMT

Well, if a three-step dynamic aggregation is what you need - then that is the way to solve it.

There is (to my knowledge) no limit to how many nested aggr:s you can have. But every nested aggregation will hit the performance, so you should avoid it if possible.

HIC