Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

inowinowski
New Contributor

Rolling Average - Including 0 in Average

Hello,

I have a table with week, SKU, and sales during that week.

I am trying to take the rolling two week average of the sales.

I have the following expression:

sum(aggr(rangesum(above( sum(sales), 0, 2)), Type)/2)

It gives me the rolling average for weeks where there are sales but does not include weeks without sales. I want the rolling average of two weeks including the weeks where there were no sales.

For example, if I have the following table:

WeekSalesRolling Average of Sales (What I am trying to achieve)Rolling Average( What expression gives me)
1777
2231515
3151919
40

7.5

0
594.512 (Avg of 15 and 9)

Any help would be greatly appreciated.

Thank you,

Isabel

1 Reply
Employee
Employee

Re: Rolling Average - Including 0 in Average

I think it would work if your data source had '0' for week4 . But if its blank and the combination comes from a join from a fixed week dimension, then it could be being treated as missing or null...in which case null+ something else is always null (and being rendered as zero).

If you replace sum(Sales) with a check for null (and assign 0) does it help  ?

sum(aggr(rangesum(above(  if( isnull(sum(sales)) , 0, sum(Sales)) , 0, 2)), Type)/2)

A sample may help to give me a few attempts to solve.

Community Browser