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

1 Solution

Accepted Solutions
sunny_talwar

How about this

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

View solution in original post

80 Replies
sunny_talwar

May be try this

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

mikegrattan
Creator III
Creator III
Author

Blank result. 

Please note that PFWarehouse is a Master item expression.  I'm using "Warehouse" in my calculations; PFWarehouse is meant to evaluate to Null if the warehouse is not in a particular match list.  So I updated your suggested formula to use Warehouse instead but got a blank result.

sunny_talwar

How about this then

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

mikegrattan
Creator III
Creator III
Author

Yes, I did try that and that's when I got the blank result.

sunny_talwar

Would you be able to share a sample?

mikegrattan
Creator III
Creator III
Author

I've attached the QVF file. Please see the "Boxes" sheet and the pivot table called "Boxes Loaded Per Day (Avg).  The pivot table is a Master Item called Boxes Loaded Per Day - Pivot.

The expression for Boxes Per day is stored in a variable called vBoxesLoadedPerDay

I also created a variable for the total of all warehouses called vBoxesLoadedPerDay_AllWarehouses

Thanks.

sunny_talwar

Don't see Boxes sheet... am I missing something obvious

Capture.PNG

mikegrattan
Creator III
Creator III
Author

Probably attached an out-dated file.  I'll delete the attachment and post again in a few minutes.

cristinapo
Contributor III
Contributor III

Hi, doesn't the total number of boxes loaded help, as below? After all, you do want the sum of all boxes loaded, regardless of PFWarehouse, Year, Date

Sum({$<RecordType={"LOAD"}>} TOTAL <PFWarehouse, Year, Date> UnitsLoaded)