topic Rangeavg bug with zero values? in QlikView

Rangeavg bug with zero values?

Anonymous — Mon, 10 Oct 2016 20:35:34 GMT

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?

Re: Rangeavg bug with zero values?

sunny_talwar — Mon, 10 Oct 2016 20:38:11 GMT

May be do RangeSum() and divide by 3?

Re: Rangeavg bug with zero values?

Anonymous — Mon, 10 Oct 2016 20:42:23 GMT

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

Re: Rangeavg bug with zero values?

sunny_talwar — Mon, 10 Oct 2016 20:51:59 GMT

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

Re: Rangeavg bug with zero values?

Anonymous — Mon, 10 Oct 2016 21:44:42 GMT

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?

Re: Rangeavg bug with zero values?

sunny_talwar — Mon, 10 Oct 2016 22:59:22 GMT

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

Re: Rangeavg bug with zero values?

Anonymous — Mon, 10 Oct 2016 23:03:47 GMT

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?

Re: Rangeavg bug with zero values?

sunny_talwar — Tue, 11 Oct 2016 01:52:04 GMT

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

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

Re: Rangeavg bug with zero values?

Anonymous — Tue, 11 Oct 2016 19:29:33 GMT

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