Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Rangeavg bug with zero values?

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?

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

8 Replies
sunny_talwar

May be do RangeSum() and divide by 3?

Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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?

Capture.JPG

sunny_talwar

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
Not applicable
Author

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