Rangeavg bug with zero values?

[Anonymous]

Hi I have a pivot table where I'm using rangeavg +  agg to calculate the last 3 months average for current month. It works like a charm when there are no zeros in any of the months, but when there are, the result is 0 even when there's data for the other 2 months.

Do you know the solution to this?

[sunny_talwar]

May be do RangeSum() and divide by 3?

[Anonymous]

I tried that, but rangesum is returning 0 as well.....

sum(aggr(Rangesum(above(sum({<MONTH1=,MONTH=,MONTH_NAME=,YEAR=,QUARTER=,Q=>}IF(SUBJECT='DELIVER', LN_COUNT,0)),0,3)),CUSTOMER_CHANNEL_GROUP,CUST_RTM_GROUP,SEGMENT,PODI_GROUPED,PLANNING_STRATEGIC_REGION_CD,MONTH))/3

[sunny_talwar]

Would you be able to share an example where it isn't working?

[Anonymous]

Here's the dashboard I have, in the container is the tab "Order", column "Den" (this is the one that calcualtes the 3 months avg)

The data for the average is the one in the tab "Delivery" column "Den"

I have 1 big fact table with a "Subject" table.

I actually noticed that there's no "DELIVER" data in Sept for MCA, this is causing the 0....

Any Idea how to fix it?

[sunny_talwar]

Where do I see MCA? I am hard time finding it in the sample. Apologize if it is right there

[Anonymous]

No worries, I didn't make myself clear. Expand the last dimension column to see it... I just ran a dummy example with an inline load..... so it won't calculate unless  at least one expression has data for Sep... in this particular neither Num nor Den has data for Sep.... I will have to generate dummy data for each record at its lowest level I guess any idea how to easily solve this?

[sunny_talwar · Accepted solution]

See if one of these work for you

RangeAvg(After(TOTAL Sum({<MONTH1=,MONTH=,MONTH_NAME=,YEAR=,QUARTER=,Q=, SUBJECT={'DELIVER'}>} LN_COUNT),0,3))

or

RangeAvg(After(TOTAL Sum({<MONTH1=,MONTH=,MONTH_NAME=,YEAR=,QUARTER=,Q=>} IF(SUBJECT='DELIVER', LN_COUNT,0)),0,3))

[Anonymous]

Thank you very much Sir. I didn't know this could be done without using aggr