Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this
Avg( Aggr( Sum(Time), Stage, $(=GetCurrentField([Hierarchy])) ) )/Count(DISTINCT Name)
Try this
Avg( Aggr( Sum(Time), Stage, $(=GetCurrentField([Hierarchy])) ) )/Count(DISTINCT Name)
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 ) )
Thanks very much Sunny! (I had to remove the inner '=' for it to work in the real example but yours worked in the sample)
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!?).