# Qlik Sense App Development

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:

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

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:

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

Tags (4)
1 Solution

Accepted Solutions
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

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

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

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

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

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

Contributor II

## Re: Incorrect average in totals line for pivot table

Yes, much better.  Thank you!

MVP

## Re: Incorrect average in totals line for pivot table

Just better, not perfect

Contributor II

## Re: Incorrect average in totals line for pivot table

Well...actually it is perfect!

MVP

## Re: Incorrect average in totals line for pivot table

I am glad it worked out