Rolling average

[hansdevr]

Hi and good morning everyone!

I have a table which shows average project throughput time in days per end of month.

What I would want, is to calculate a "rolling average", but with a twist..

Month 1 should show 38.4, month 2 should show the average of sum of months 1 and 2, and so on.

In the new year, this should be reset and start over again with just the average of month 1.

What would be the best way of achieving this? Through set analysis? Load script?

Any help would be greatly appreciated!

[Anil_Babu_Samineni]

Does this work for you?

RangeAvg(above(TOTAL Avg([Throughput time in days]),0,RowNo(TOTAL)))

[hansdevr]

Thank you, Anil! Wil try that!

[hansdevr]

Looks good, but I missed the "reset" in jan 2017...

[Anil_Babu_Samineni]

Is that question to me, or just telling to me..

[Frank_Hartmann]

try this:

RangeAvg(above(TOTAL Avg([Throughput time in days]),0,RowNo()))

hope this helps

[hansdevr]

No, it was a well-meant question: how to reset the average @ beginning of the new year..

[hansdevr]

Hi Frank,

Your answer rendered the averages that already were in the table...

[Anil_Babu_Samineni]

May be this?

If(Month <> Month(YearStart(Today())), RangeAvg(above(TOTAL Avg([Throughput time in days]),0,RowNo(TOTAL))))

OR

RangeAvg(above(TOTAL Avg({<Month -= {'$(=Min(Month))'}>} [Throughput time in days]),0,RowNo(TOTAL)))

[qliksus]

As you want to reset the value every time the year changes total shouldn't  be use

RangeAvg(above(Avg([Throughput time in days]),0,RowNo()))