Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
oscarmqz
Creator III
Creator III

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?

oscarmqz
Creator III
Creator III
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?

oscarmqz
Creator III
Creator III
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

oscarmqz
Creator III
Creator III
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))

View solution in original post

oscarmqz
Creator III
Creator III
Author

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