topic Re: Incorrect average total in pivot table in App Development

Incorrect average total in pivot table

Anonymous — Thu, 18 Aug 2016 16:07:20 GMT

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?

Re: Incorrect average total in pivot table

sunny_talwar — Thu, 18 Aug 2016 16:26:13 GMT

Can you try this:

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

Re: Incorrect average total in pivot table

kaushiknsolanki — Thu, 18 Aug 2016 16:27:47 GMT

Hi,

Try this expression,

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

Regards,

Kaushik Solanki

Re: Incorrect average total in pivot table

Anonymous — Fri, 19 Aug 2016 06:39:07 GMT

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

Re: Incorrect average total in pivot table

sunny_talwar — Fri, 19 Aug 2016 08:31:14 GMT

Would you be able to share a sample?

Re: Incorrect average total in pivot table

Anonymous — Fri, 19 Aug 2016 11:11:13 GMT

No problem. See attachment.

Re: Incorrect average total in pivot table

kaushiknsolanki — Fri, 19 Aug 2016 13:06:04 GMT

Hi,

The expression works well.

Here is the QVF.

Also the Screenshot.

Regards,

Kaushik Solanki

Re: Incorrect average total in pivot table

sunny_talwar — Fri, 19 Aug 2016 13:10:18 GMT

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

Re: Incorrect average total in pivot table

Anonymous — Fri, 19 Aug 2016 14:30:20 GMT

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.