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