Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

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

View solution in original post

6 Replies
sunny_talwar

Try this

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

Not applicable
Author

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.

sunny_talwar

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
Author

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,

sunny_talwar

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

Not applicable
Author

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

Regards