Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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:

Example.PNG

Thank you in advanced

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

1 Solution

Accepted Solutions

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))

6 Replies

Re: Handling missing values in Average total mode pivot table

Try this

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

Not applicable

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))

Not applicable

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?

Not applicable

Re: Handling missing values in Average total mode pivot table

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

Regards

Community Browser