# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Contributor 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!

Tags (1)
1 Solution

Accepted Solutions
MVP

## Re: Rolling average

Try this

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

27 Replies

## Re: Rolling average

Does this work for you?

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

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

## Re: Rolling average

Thank you, Anil! Wil try that!

Contributor III

## 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..

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)
Honored Contributor II

## Re: Rolling average

try this:

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

hope this helps

Contributor III

## Re: Rolling average

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

Contributor III

Hi Frank,

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

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)
Valued Contributor II

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