Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
YouriGeurkink
Contributor
Contributor

Sum of values over a x-period of days

I'm looking for a method to get the moving average over a period of x days. I added a picture with the data, which shows that there a days with values greater than zero, values that are equal to zero and days without data. On the days without value, the actual value of that day can be regarded as zero.

Any help is much appreciated!

Labels (2)
4 Replies
124rooski
Partner - Creator
Partner - Creator

Having trouble seeing your picture (probably just me). You should be able to get the moving avg using the calc below.

rangeavg(above(sum(sales),0,6))

If that doesn't work I'm guessing it may have to do with null values which you can easily fix by adding something like this in the script: IF(VALUE= 'NULL', 0, '-') as newFieldName

 

 

YouriGeurkink
Contributor
Contributor
Author

Hi 124Rooski,

 

Thanks for you reply!

The main problem seems to be changing the '-' into zero-values. Your proposed solution also didn't work for me. 

Do you have any clue why the '-' cannot be changed into zero's? If I can solve this problem, the moving average is probably easily solved after.

124rooski
Partner - Creator
Partner - Creator

Do you have a .qvf you can show as an example? I found this post that may help you as well. 

https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394 

YouriGeurkink
Contributor
Contributor
Author

I've attached a qvf.