topic Re: AVG with missing month in QlikView

AVG with missing month

zlong1 — Mon, 26 Jan 2026 18:19:17 GMT

Please take a look at the following pic. Because there is no data in JUNE, the average line 1059 is only calculated based on 9 months, like (sum from JAN to OCT / 9).

The correct outcome should be 953 which is (sum from JAN to OCT / 10).

Because the monthyear dimension is necessary, is there any way that I can get correct average value in this single chart?

Re: AVG with missing month

Gopi_E — Tue, 19 Nov 2019 06:15:01 GMT

try to check supressed values are hidden

Re: AVG with missing month

zlong1 — Tue, 19 Nov 2019 22:19:40 GMT

I didn't supress any value. There is no order in JUNE. So when I generate the chart, there is no JUNE bar. But when I calculate the average, I need the sum of ten months / 10. However, the QV calculate like sum of 10 months / 9 because of no data in JUNE.

Re: AVG with missing month

Brett_Bleess — Tue, 03 Dec 2019 22:12:19 GMT

Zilong, have a look at the following Design Blog link, I think it may help you get things resolved:

https://community.qlik.com/t5/Qlik-Design-Blog/Average-Which-average/ba-p/1466654

Regards,
Brett

Re: AVG with missing month

Arthur_Fong — Wed, 04 Dec 2019 03:06:26 GMT

Maybe you should try instead of using qlik native average function:

sum(<Data>)/count(distinct Month)

Be sure you have a master calendar designed in your data model.