Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register 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