27 Replies Latest reply: Sep 25, 2017 8:26 AM by Hans de Vries

# Rolling average

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!

• ###### Re: Rolling average

Does this work for you?

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

• ###### Re: Rolling average

Thank you, Anil! Wil try that!

• ###### Re: Rolling average

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

• ###### Re: Rolling average

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

• ###### Re: Rolling average

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

• ###### Re: Rolling average

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

• ###### Re: Rolling average

You were closer the first time.. Now I get "gaps" in the output...

• ###### Re: Rolling average

Because, you added second issue over initial thread. Can you tell us expected result.

• ###### Re: Rolling average

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.

This second clause was already there...

• ###### Re: Rolling average

That is where it works.. Will you attach excel file instead image so then we can look

• ###### Re: Rolling average

this would be the desired result...

• ###### Re: Rolling average

See attached...

• ###### Re: Rolling average

Did you give this a shot?

Aggr(RangeAvg(Above(Throughput time in days expression here, 0, RowNo())), Year, Month)

• ###### Re: Rolling average

try this:

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

hope this helps

• ###### Re: Rolling average

Hi Frank,

• ###### Re: Rolling average

would you be able to share a sample qvw?

• ###### Re: Rolling average

Dear,

can you try

For Average:

=numavg(above([Throughput time in Days],0,(aggr(RowNo(),Year,Month))))

For Sum:

=numsum(above([Throughput time in Days],0,(aggr(RowNo(),Year,Month))))

Kindly find the attached sample Application.

Thanks,

Mukram

• ###### Re: Rolling average

Dear @Hans de Vries

have you checked my attached application?

• ###### Re: Rolling average

I am sorry had to attend a meeting... Will check ASAP

• ###### Re: Rolling average

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

• ###### Re: Rolling average

Again, this renders the same numbers as found in the column [Throughput time in days]...

• ###### Re: Rolling average

Are you using a straight or Pivot table? If you use  pivot table the rows should be resetted when there is a new value.  Look the  below case when the category field changes it just reset to the new value

RangeAvg( Above(avg({<Y= {\$(=Max(Y))}>}amount )-Sum ({<Y= {\$(=Max(Y)-1)}>}[Venta Neta]),0,RowNo()))

• ###### Re: Rolling average

Try this

Aggr(RangeAvg(Above(Throughput time in days expression here, 0, RowNo())), Year, Month)

• ###### Re: Rolling average

Thanks Sunny, that did the trick!

• ###### Re: Rolling average

Thanks everyone for thinking with me to solve this!! Thanks again!