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
sunny_talwar

Will try to check it out today.

vvira1316
Specialist II
Specialist II

Hi Mike,

I/m not sure how I got that screen shot. Unfortunately I didn't save it and lost it when I came in next morning. I'll keep trying and let you know if I succeed in replicating.

Sorry

BR,

Vijay

mikegrattan
Creator III
Creator III
Author

Hi Sunny,

Hoping you have had an opportunity to review this a little more?  Thanks!

sunny_talwar

Nope, checking it now... should get back to you in 10 mins with questions (if I have any)

mikegrattan
Creator III
Creator III
Author

Thanks, I appreciate your help very much! 

sunny_talwar

Try this

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


Capture.PNG

mikegrattan
Creator III
Creator III
Author

pivot table total the average for each warehouse.jpgThat expression gives the overall average for all warehouses.  I'm looking for the sum of the warehouse averages.  In your screenshot, that would be the sum of HUR, SAL, SBF, SSS, YBF, YSS, and YUM.  The grand total of the warehouses is 907,342.

sunny_talwar

Change Avg to Sum

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

mikegrattan
Creator III
Creator III
Author

I should have mentioned that I tried Sum instead of Avg in your expression and I got the number for the entire year instead of a daily avg total.  The number I'm getting is 39,605,548. 

Thanks.

mikegrattan
Creator III
Creator III
Author

Hi Sunny,

I've tried multiple variations of expressions and still not able to get it to do what I want. 

I even tried wrapping the daily average expression (vBoxesLoadedPerDay) in a Sum(Aggr()) and got interesting results but not what I'm looking for.  Any other suggestion you can think of?

Thank you!