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

Problem summing an average in pivot table

I have a pivot table with a measure that calculates the average number of boxes shipped per day by each warehouse.  I'd like to get a sum of those averages by warehouse so I can then do a percentage of total calculation.  The current expression to get average of boxes per day is:

Avg(Aggr(Sum({$<RecordType={"LOAD"}>} UnitsLoaded),Date))

I've tried modifying that expression in order to get the total for all warehouses but haven't been able to get any variations to work as I expect.

Avg(Aggr(Sum({$<RecordType={"LOAD"},Warehouse=>} UnitsLoaded),Date))

Avg(Aggr(Sum({1<RecordType={"LOAD"}>} UnitsLoaded),Date))

Sum(Aggr(Avg({$<RecordType={"LOAD"}>} UnitsLoaded),Date))

etc.

pivot table average totals by Warehouse.jpg

80 Replies
mikegrattan
Creator III
Creator III
Author

Attachment has been updated.

mikegrattan
Creator III
Creator III
Author

Logically that seems like it should work; unfortunately, it gives a blank result.

sunny_talwar

Don't see the attachment

mikegrattan
Creator III
Creator III
Author

I guess it's still loading?  If it doesn't show up in a few minutes I'll upload again.

mikegrattan
Creator III
Creator III
Author

Updated attachment should be there now.

vvira1316
Specialist II
Specialist II

Hi,

Are you expecting following information?Total.PNG

mikegrattan
Creator III
Creator III
Author

Hi Vijay,

No, the totals you have look like the totals for the whole year.  I'm trying to get a total of all of the daily averages for each warehouse.  Thanks.

mikegrattan
Creator III
Creator III
Author

Hi Sunny.  Have you had a chance to look at the new attachment I loaded?  Thank you.

.

vvira1316
Specialist II
Specialist II

Hi,

Is your requirement as follows

   

PFWarehouseAvg Boxes Loaded Per DayTotal%
HUR125,098928,15113.48%
SAL139,650928,15115.05%
SBF131,870928,15114.21%
SSS134,232928,15114.46%
YBF148,953928,15116.05%
YSS123,223928,15113.28%
YUM125,125928,15113.48%

Formula For Avg Boxes Loaded Per Day is Avg(Aggr(Sum({$<RecordType={"LOAD"}>} UnitsLoaded),Date))

Total is sum of each of those individual values for warehouse

mikegrattan
Creator III
Creator III
Author

That is the behavior I'm looking for.  Are you able to replicate that behavior in a pivot table?