Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

mikegrattan
Contributor II

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

Re: Problem summing an average in pivot table

How about this

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

80 Replies

Re: Problem summing an average in pivot table

May be try this

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

mikegrattan
Contributor II

Re: Problem summing an average in pivot table

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.

Re: Problem summing an average in pivot table

How about this then

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

mikegrattan
Contributor II

Re: Problem summing an average in pivot table

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

Re: Problem summing an average in pivot table

Would you be able to share a sample?

mikegrattan
Contributor II

Re: Problem summing an average in pivot table

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.

Re: Problem summing an average in pivot table

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

Capture.PNG

mikegrattan
Contributor II

Re: Problem summing an average in pivot table

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

cristinapo
New Contributor III

Re: Problem summing an average in pivot table

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)


Community Browser