Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
How about this
Sum(TOTAL <Year>Aggr(Avg(Aggr(Sum({$<RecordType={"LOAD"}>} UnitsLoaded),Date,Warehouse)), Warehouse, Year))
May be try this
Sum(TOTAL <Year>Aggr(Sum({$<RecordType={"LOAD"}>} UnitsLoaded), PFWarehouse, Year, Date))
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.
How about this then
Sum(TOTAL <Year>Aggr(Sum({$<RecordType={"LOAD"}>} UnitsLoaded), Warehouse, Year, Date))
Yes, I did try that and that's when I got the blank result.
Would you be able to share a sample?
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.
Don't see Boxes sheet... am I missing something obvious
Probably attached an out-dated file. I'll delete the attachment and post again in a few minutes.
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)