# 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:

Thank you in advanced

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

1 Solution

Accepted Solutions
MVP

## 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
MVP

## 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.

MVP

## 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,

MVP

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