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: 
phoenix
Creator
Creator

Rolling 6 month sum/average returns incorrect values when current months values is Zero

Hi, I am having problem calculating Rolling 6 month sum and average if the current months value is Zero. For sum it returns 0 and for average it returns a null. Is there any option that I could use to make the set analysis to consider zero values? Thanks!

I am using below expressions:

Rolling 6 MO sum:

 

sum(aggr(rangesum(above(count({<ORDER_TYPE={VR'},order_flag={'1'}>}DISTINCT CASE_NUMBER),0,6)),COMPANY_NAME,Transaction_Date_YR_MTH))

Rolling 6 MO average:

 

avg(aggr(rangeavg(above(count({<ORDER_TYPE={'RV'},ORDER_STATUS={'O','H'}>}DISTINCT CASE_NUMBER),0,6)),COMPANY_NAME,Transaction_Date_YR_MTH))

0 Replies