Aggr() with drill down

[rutger_jansen]

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

[sunny_talwar · Accepted solution]

Try this

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

[swuehl]

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 ) )

[rutger_jansen]

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

[rutger_jansen]

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!?).