topic Re: Rolling average in QlikView

Rolling average

hansdevr — Mon, 25 Sep 2017 09:18:11 GMT

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

Anil_Babu_Samineni — Mon, 25 Sep 2017 09:23:13 GMT

Does this work for you?

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

Re: Rolling average

hansdevr — Mon, 25 Sep 2017 09:33:32 GMT

Thank you, Anil! Wil try that!

Re: Rolling average

hansdevr — Mon, 25 Sep 2017 09:41:43 GMT

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

Re: Rolling average

Anil_Babu_Samineni — Mon, 25 Sep 2017 09:46:34 GMT

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

Re: Rolling average

Frank_Hartmann — Mon, 25 Sep 2017 09:58:46 GMT

try this:

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

hope this helps

Re: Rolling average

hansdevr — Mon, 25 Sep 2017 10:10:31 GMT

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

Re: Rolling average

hansdevr — Mon, 25 Sep 2017 10:13:23 GMT

Hi Frank,

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

Re: Rolling average

Anil_Babu_Samineni — Mon, 25 Sep 2017 10:25:23 GMT

May be this?

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

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

Re: Rolling average

qliksus — Mon, 25 Sep 2017 10:29:15 GMT

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

hansdevr — Mon, 25 Sep 2017 10:32:53 GMT

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

Re: Rolling average

hansdevr — Mon, 25 Sep 2017 10:39:26 GMT

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

Re: Rolling average

Frank_Hartmann — Mon, 25 Sep 2017 10:45:48 GMT

would you be able to share a sample qvw?

Re: Rolling average

Anil_Babu_Samineni — Mon, 25 Sep 2017 10:47:29 GMT

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

Re: Rolling average

hansdevr — Mon, 25 Sep 2017 10:51:24 GMT

You're mistaken. I added nothing.

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

Anil_Babu_Samineni — Mon, 25 Sep 2017 10:53:00 GMT

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

Re: Rolling average

qliksus — Mon, 25 Sep 2017 10:55:35 GMT

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

mdmukramali — Mon, 25 Sep 2017 10:58:41 GMT

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

hansdevr — Mon, 25 Sep 2017 11:01:17 GMT

Re: Rolling average

hansdevr — Mon, 25 Sep 2017 11:01:51 GMT

this would be the desired result...