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
vvira1316
Specialist II
Specialist II

Thanks. I felt it was a genuine set analysis issue but at the same token when I saw the QlikSense file with numerous calculation, the data model I felt something about data issue and hence kept going towards raw data.

Always felt that Sunny's response in such situation are most of the time on target and I was surprised that why it was not working, that is when I took it on as a challenge to address it.

I'm glad that our effort paid off in finding right solution with right data as that is more crucial.

All the best.

BR,

Vijay