Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
May be try this
Avg(Aggr(Avg(Aggr(Count(Distinct CaseNumber), Warehouse,Year,Date)), Warehouse, Year))
Date is not one of the dimension, why use it in Aggr() function.... May be this
Avg(Aggr(Count(Distinct CaseNumber), Warehouse,Year))
Date is not in the pivot table, but I want a daily average of pallets per warehouse.
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
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.
May be try this
Avg(Aggr(Avg(Aggr(Count(Distinct CaseNumber), Warehouse,Year,Date)), Warehouse, Year))
Yes, much better. Thank you!
Just better, not perfect
Well...actually it is perfect!
I am glad it worked out