Incorrect average total in pivot table

[Anonymous]

Hi,

I use the following measure to calculate my percentages per Year/Month:
Count(distinct [PurchDoc])/count(total< [Year/Month] >distinct [PurchDoc])

I expected the pivot table to calculate the average of the percentages for each column in the totals. Yet when I compare the totals from Qlik Sense with the average I calculated in Excel there is a difference for one column (53% instead of 56%).

Why is this?

[sunny_talwar]

Can you try this:

Avg(Aggr(Count(distinct [PurchDoc])/count(total< [Year/Month] >distinct [PurchDoc]), [Year/Month], [Buying Channel]))

[kaushiknsolanki]

Hi,

Try this expression,

Avg(Aggr(Count(distinct [PurchDoc])/count(total< [Year/Month] >distinct [PurchDoc]),[Buying Channel]))

Regards,

Kaushik Solanki

[Anonymous]

I tried both expressions but it still displays an incorrect average of 53%.

[sunny_talwar]

Would you be able to share a sample?

[Anonymous]

No problem. See attachment.

[kaushiknsolanki · Accepted solution]

Hi,

The expression works well.

Here is the QVF.

Also the Screenshot.

Regards,

Kaushik Solanki

[sunny_talwar]

Precisely, I think you might have just checked one expression and forgot to check mine. This one does seem to work:

Avg(Aggr(Count(distinct [PurchDoc])/count(total< [Year/Month] >distinct [PurchDoc]), [Year/Month], [Buying Channel]))

[Anonymous]

You're right. The expression works well. I was just implementing it in the wrong area. I thought it was to be inserted in the totals.