How to make the mean by year?

Stefano_Mazzocca · Mar 9, 2026 10:51AM

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?

Stefano_Mazzocca · Mar 9, 2026 11:45AM

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

Raghavendra Raghunath-Oracle · Mar 9, 2026 10:55AM

"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

Stefano_Mazzocca · Mar 9, 2026 11:27AM

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

Oracle Fusion AI Data Platform Forum