Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

mikegrattan
Contributor II

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

Re: Incorrect average in totals line for pivot table

May be try this

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

9 Replies
MVP
MVP

Re: Incorrect average in totals line for pivot table

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

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

mikegrattan
Contributor II

Re: Incorrect average in totals line for pivot table

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

MVP
MVP

Re: Incorrect average in totals line for pivot table

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
Contributor II

Re: Incorrect average in totals line for pivot table

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.

MVP
MVP

Re: Incorrect average in totals line for pivot table

May be try this

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

mikegrattan
Contributor II

Re: Incorrect average in totals line for pivot table

Yes, much better.  Thank you!

MVP
MVP

Re: Incorrect average in totals line for pivot table

Just better, not perfect

mikegrattan
Contributor II

Re: Incorrect average in totals line for pivot table

Well...actually it is perfect! 

MVP
MVP

Re: Incorrect average in totals line for pivot table

I am glad it worked out

Community Browser