topic Calculate the average with missing data in QlikView

Calculate the average with missing data

Fri, 29 Jan 2010 12:21:49 GMT

Hi all,

I have a dataset which only stores monthly sales of materials when actual sales have occured.

When calculating the average, Qlikview doesn't take the months without sales into account.

I'm looking for a solution to calculate the average correct without adding extra records for the months without sales.

Here is screenshot of the situation:

The average sales based on the 4 months should be 87,5. Now Qlikview is only considering months 1-3.

Do you have any ideas? Thanks.

James.

Calculate the average with missing data

amit_shetty78 — Fri, 29 Jan 2010 12:47:54 GMT

Hi James,

Please try the following expression instead of AVG(Sales).

=sum(Sales)/count(TOTAL Month)

Rgds,

Calculate the average with missing data

Fri, 29 Jan 2010 13:13:36 GMT

Hi Amit,

When selection is made on material 1, then average is still not correct.

See screenshot:

Kind regards,

James.

Calculate the average with missing data

amit_shetty78 — Fri, 29 Jan 2010 14:51:11 GMT

Hi James,

This is because Material 1 is not associated to month 4. You would have noticed that when Material 1 was explicitly selected '4' was excluded from the month's selection making the count of months to 3.

One suggestion could be that instead of keeping the sales value in month 4 blank, you can put in sales value as 0 for Material 1 in the 4th Month.

Hope this helps.

Rgds, Amit.

Calculate the average with missing data

Sun, 31 Jan 2010 00:18:24 GMT

Hi Amit,

In order to keep the number of records as small as possible, I don't want to add records with zero sales as I mentioned before.

Kind regards,

James.