topic Re: Incorrect average in totals line for pivot table in App Development

Incorrect average in totals line for pivot table

mikegrattan — Thu, 14 Sep 2017 17:22:31 GMT

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?

Re: Incorrect average in totals line for pivot table

sunny_talwar — Thu, 14 Sep 2017 17:28:10 GMT

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

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

Re: Incorrect average in totals line for pivot table

mikegrattan — Thu, 14 Sep 2017 17:30:00 GMT

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

Re: Incorrect average in totals line for pivot table

sunny_talwar — Thu, 14 Sep 2017 17:37:12 GMT

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

Re: Incorrect average in totals line for pivot table

mikegrattan — Thu, 14 Sep 2017 17:44:40 GMT

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.

Re: Incorrect average in totals line for pivot table

sunny_talwar — Thu, 14 Sep 2017 17:51:07 GMT

May be try this

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

Re: Incorrect average in totals line for pivot table

mikegrattan — Thu, 14 Sep 2017 19:41:45 GMT

Yes, much better. Thank you!

Re: Incorrect average in totals line for pivot table

sunny_talwar — Thu, 14 Sep 2017 19:42:19 GMT

Just better, not perfect

Re: Incorrect average in totals line for pivot table

mikegrattan — Thu, 14 Sep 2017 19:45:22 GMT

Well...actually it is perfect!

Re: Incorrect average in totals line for pivot table

sunny_talwar — Thu, 14 Sep 2017 19:46:19 GMT

I am glad it worked out