topic Re: Aggr() with drill down in QlikView

Aggr() with drill down

rutger_jansen — Tue, 23 Oct 2018 16:52:14 GMT

Hi,

Please find attached some sample data; I'm struggling to get the syntax right for this aggr() expression.

Intention is to sum Time in each stage per Name and show the average of time in stage at higher levels of aggregation (in real world there are multiple hierarchical levels and some set analysis, in the example just 'TEAM'). Currently I have:

Avg( Aggr( Sum(Time), Stage, $(=GetCurrentField([Hierarchy])) ) )

The desired result:

TEAM	A	B	C
X	23.5	8	5
Y	6	10	2

and (drilling down from X):

Name	A	B	C
Bill	30	9	4
Harry	17	7	6

Any help would be great,

Thanks,

Rutger

Re: Aggr() with drill down

sunny_talwar — Tue, 23 Oct 2018 16:59:52 GMT

Try this

Avg( Aggr( Sum(Time), Stage, $(=GetCurrentField([Hierarchy])) ) )/Count(DISTINCT Name)

Re: Aggr() with drill down

swuehl — Tue, 23 Oct 2018 17:15:03 GMT

Maybe your real word problem is more complex, but it should also be sufficient to use

Avg( Aggr( Sum(Time), Stage, TEAM,Name ) )

edit:

Or even

Avg( Aggr( Sum(Time), Stage, Name ) )

Re: Aggr() with drill down

rutger_jansen — Tue, 23 Oct 2018 17:23:07 GMT

Thanks very much Sunny! (I had to remove the inner '=' for it to work in the real example but yours worked in the sample)

Re: Aggr() with drill down

rutger_jansen — Tue, 23 Oct 2018 17:37:38 GMT

Thanks Stefan, much appreciated. I prefer this answer because I didn't really understand why the avg() needed to be divided by a count (though it worked!?).