Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hansdevr
Creator III
Creator III

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!

1 Solution

Accepted Solutions
sunny_talwar

Try this

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

View solution in original post

27 Replies
Anil_Babu_Samineni

Does this work for you?

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
hansdevr
Creator III
Creator III
Author

Thank you, Anil! Wil try that!

hansdevr
Creator III
Creator III
Author

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

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Frank_Hartmann
Master II
Master II

try this:

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

hope this helps

hansdevr
Creator III
Creator III
Author

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

hansdevr
Creator III
Creator III
Author

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
qliksus
Specialist II
Specialist II

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