topic Re: Monthly Average without Every Month in Data in QlikView

Monthly Average without Every Month in Data

cranium144 — Wed, 25 Nov 2020 16:16:04 GMT

Hi.

I have an example where I'm doing a calculation to get the average number sold per month. The calculation is:

sum(qtySold) / count(DISTINCT MonthYear)

However, this causes an issue when I select say Year 2017. A particular customer only had sales in 6 months. So here is what I'm getting as a result, and what I want, assuming a Qty Sold of 1,200.

Actual Result: 200 / month

Wanted Result: 100 / month

Any suggestions on how to get the 'true' average based on the total month span? (including across years)

Thanks!

Rick

Re: Monthly Average without Every Month in Data

sunny_talwar — Thu, 15 Feb 2018 15:27:50 GMT

May be this

Sum(qtySold)/Count(DISTINCT TOTAL MonthYear)

Re: Monthly Average without Every Month in Data

cranium144 — Thu, 15 Feb 2018 15:37:01 GMT

I like the idea. However, I still received the same result. Only 6 MonthYear's of data exist. I'm attempting to determine how many possible MonthYear's exist in the selections.

Re: Monthly Average without Every Month in Data

sunny_talwar — Thu, 15 Feb 2018 15:46:07 GMT

Have you selected a particular customer? May be ignore selection in Customer field

Sum(qtySold)/Count(DISTINCT TOTAL {<Customer>} MonthYear)

Re: Monthly Average without Every Month in Data

cranium144 — Thu, 15 Feb 2018 16:21:14 GMT

That seems to have done the trick. Thank you!