topic Re: Handling missing values in Average total mode pivot table in QlikView

Handling missing values in Average total mode pivot table

Tue, 12 Sep 2017 16:52:39 GMT

Hi,

Im trying to perform an average in a pivot table. Mi problem is that i can´t get de correct result in totals because there are not registers for all the months. The first employee only has data for Jun so the total doesnt count July and Ago. I know it´s working ok in Qlik, but i need Qlik thinks there are 0s in these months. So the result for the first row must be 0,51.

Example:

Thank you in advanced

My expression is 'sum({<ConceptoNCod={'940'}>}KPI03)/Count(DISTINCT MesAño)'

Re: Handling missing values in Average total mode pivot table

sunny_talwar — Tue, 12 Sep 2017 16:53:46 GMT

Try this

Sum({<ConceptoNCod={'940'}>} KPI03)/Count(TOTAL DISTINCT MesAño)

Re: Handling missing values in Average total mode pivot table

Tue, 12 Sep 2017 16:59:57 GMT

Hi Sunny,

Thank you for answer the question so fast.

This solution doesnt work because it means all columns are going to be diveded by 3, not only the total.

Re: Handling missing values in Average total mode pivot table

sunny_talwar — Tue, 12 Sep 2017 17:02:56 GMT

That is true, in that case you can make use dimensionlity() function

If(SecondaryDimensionality() = 0, Sum({<ConceptoNCod={'940'}>} KPI03)/Count(TOTAL DISTINCT MesAño),

Sum({<ConceptoNCod={'940'}>} KPI03)/Count(DISTINCT MesAño))

Re: Handling missing values in Average total mode pivot table

Wed, 13 Sep 2017 11:39:30 GMT

Hi Sunny,

Is not working because the value of 'Ago2017' is not 0 and is not null. There are not data for the employee for this month in the model.

Regards,

Re: Handling missing values in Average total mode pivot table

sunny_talwar — Wed, 13 Sep 2017 11:49:48 GMT

Not sure I understand.... do you mind sharing a sample?

Re: Handling missing values in Average total mode pivot table

Wed, 13 Sep 2017 15:56:16 GMT

Sorry!! It was ok! Thank you very much.

Regards