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: 
Anonymous
Not applicable

Incorrect average total in pivot table

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?

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

The expression works well.

Here is the QVF.

Also the Screenshot.

Capture.PNG

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

8 Replies
sunny_talwar

Can you try this:

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Try this expression,

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


Regards,

Kaushik Solanki


Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Anonymous
Not applicable
Author

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

sunny_talwar

Would you be able to share a sample?

Anonymous
Not applicable
Author

No problem. See attachment.

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

The expression works well.

Here is the QVF.

Also the Screenshot.

Capture.PNG

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
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
Not applicable
Author

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.