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: 
mikegrattan
Creator III
Creator III

Incorrect average in totals line for pivot table

I'm using the following formula:

Avg(Aggr(Count(Distinct CaseNumber), Warehouse,Year,Date))

The pivot table comes out like this:

pivot table average totals.jpg

When I try to validate the totals line in Excel, I am getting different numbers:

pivot table average totals_excel.jpg

I am currently hiding Nulls in this table for Warehouse.  PFWarehouse is a calculated master dimension:

=If(Match(Warehouse,'HUR','SAL','SSS','SBF','YUM','YBF','YSS'),Warehouse,Null())

If I do not exclude Nulls from the pivot table I get the following data:

pivot table average totals_with nulls.jpg

Qlik is adjusting the totals, so I don't think the Null warehouse is throwing this off.  Perhaps my formula needs some fine tuning?

1 Solution

Accepted Solutions
sunny_talwar

May be try this

Avg(Aggr(Avg(Aggr(Count(Distinct CaseNumber), Warehouse,Year,Date)), Warehouse, Year))

View solution in original post

9 Replies
sunny_talwar

Date is not one of the dimension, why use it in Aggr() function.... May be this

Avg(Aggr(Count(Distinct CaseNumber), Warehouse,Year))

mikegrattan
Creator III
Creator III
Author

Date is not in the pivot table, but I want a daily average of pallets per warehouse.

sunny_talwar

In that case, I think your row level numbers might not be correct.... you probably should use Avg(Aggr(Count(Distinct CaseNumber), Warehouse,Year,Date)) for your pivot table also

mikegrattan
Creator III
Creator III
Author

I'm not sure I understand what you're saying.  I am using that formula in my pivot table.  I just don't have the Date dimension showing in my pivot table. 

If I add Date to the pivot table either as a row or a column I am still seeing the same numbers at the row level and in the Totals row.

sunny_talwar

May be try this

Avg(Aggr(Avg(Aggr(Count(Distinct CaseNumber), Warehouse,Year,Date)), Warehouse, Year))

mikegrattan
Creator III
Creator III
Author

Yes, much better.  Thank you!

sunny_talwar

Just better, not perfect

mikegrattan
Creator III
Creator III
Author

Well...actually it is perfect! 

sunny_talwar

I am glad it worked out