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

phoenix — Wed, 25 Nov 2020 16:16:04 GMT

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))

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

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