6 Replies Latest reply: Sep 13, 2017 11:56 AM by Santiago S?nchez

# Handling missing values in Average total mode pivot table

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:

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

• ###### Re: Handling missing values in Average total mode pivot table

Try this

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

• ###### Re: Handling missing values in Average total mode pivot table

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

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

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

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

• ###### Re: Handling missing values in Average total mode pivot table

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

Regards