I'm trying to build a metric that gives the mean for each year, for each person number.
Let's say I want to do it on FTE (but it could be any number) and the situation is the following, how can I do?
The correct formula seems to be the following, I didn't realise it could take more than 1 parameter :)
AGGREGATE(Avg("FTE") BY "Person Number", "Year")
Hi @Stefano_Mazzocca
"mean for each year, for each person number"
the formula for mean is (sum of all data points)/(Count of data points)
how can this change for each person? can you help us understand so that we can provide an answer
Raghu
Hi @Raghavendra Raghunath-Oracle ,
For every Year and for every Person I need to perform the average.
Let's say PN_1 has FTE = 1 for 11 months and FTE = 0.5 for 1 month, I need (1*11+0.5*1)/12=0.96
Following year PN_1 may have 0.5 for 5 months and 1 for 7months so I need (0.5*5+1*7)/12=0.79
On the other hand PN_2 may have different values so I need to aggregate for PN and Year.
So far I tried the following formula but it is not performing well:
AGGREGATE(AGGREGATE(Avg("FTE") BY ""Person Number") BY "Year")
