topic Re: Problem summing an average in pivot table in App Development

Problem summing an average in pivot table

mikegrattan — Mon, 09 Oct 2017 16:50:45 GMT

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.

Re: Problem summing an average in pivot table

sunny_talwar — Mon, 09 Oct 2017 16:55:57 GMT

May be try this

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

Re: Problem summing an average in pivot table

mikegrattan — Mon, 09 Oct 2017 17:01:42 GMT

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

sunny_talwar — Mon, 09 Oct 2017 17:02:49 GMT

How about this then

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

Re: Problem summing an average in pivot table

mikegrattan — Mon, 09 Oct 2017 17:04:53 GMT

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

Re: Problem summing an average in pivot table

sunny_talwar — Mon, 09 Oct 2017 17:08:37 GMT

Would you be able to share a sample?

Re: Problem summing an average in pivot table

mikegrattan — Mon, 09 Oct 2017 17:21:37 GMT

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

sunny_talwar — Mon, 09 Oct 2017 19:01:44 GMT

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

Re: Problem summing an average in pivot table

mikegrattan — Mon, 09 Oct 2017 19:16:08 GMT

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

Re: Problem summing an average in pivot table

cristinapo — Mon, 09 Oct 2017 19:18:40 GMT

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)

Re: Problem summing an average in pivot table

mikegrattan — Mon, 09 Oct 2017 19:23:37 GMT

Attachment has been updated.

Re: Problem summing an average in pivot table

mikegrattan — Mon, 09 Oct 2017 19:26:23 GMT

Logically that seems like it should work; unfortunately, it gives a blank result.

Re: Problem summing an average in pivot table

sunny_talwar — Mon, 09 Oct 2017 19:32:52 GMT

Don't see the attachment

Re: Problem summing an average in pivot table

mikegrattan — Mon, 09 Oct 2017 19:35:46 GMT

I guess it's still loading? If it doesn't show up in a few minutes I'll upload again.

Re: Problem summing an average in pivot table

mikegrattan — Mon, 09 Oct 2017 19:41:57 GMT

Updated attachment should be there now.

Re: Problem summing an average in pivot table

vvira1316 — Mon, 09 Oct 2017 21:27:00 GMT

Hi,

Are you expecting following information?

Re: Problem summing an average in pivot table

mikegrattan — Mon, 09 Oct 2017 21:31:11 GMT

Hi Vijay,

No, the totals you have look like the totals for the whole year. I'm trying to get a total of all of the daily averages for each warehouse. Thanks.

Re: Problem summing an average in pivot table

mikegrattan — Tue, 10 Oct 2017 21:38:00 GMT

Hi Sunny. Have you had a chance to look at the new attachment I loaded? Thank you.

Re: Problem summing an average in pivot table

vvira1316 — Tue, 10 Oct 2017 21:54:54 GMT

Hi,

Is your requirement as follows

PFWarehouse	Avg Boxes Loaded Per Day	Total	%
HUR	125,098	928,151	13.48%
SAL	139,650	928,151	15.05%
SBF	131,870	928,151	14.21%
SSS	134,232	928,151	14.46%
YBF	148,953	928,151	16.05%
YSS	123,223	928,151	13.28%
YUM	125,125	928,151	13.48%

Formula For Avg Boxes Loaded Per Day is Avg(Aggr(Sum({$<RecordType={"LOAD"}>} UnitsLoaded),Date))

Total is sum of each of those individual values for warehouse

Re: Problem summing an average in pivot table

mikegrattan — Tue, 10 Oct 2017 21:58:50 GMT

That is the behavior I'm looking for. Are you able to replicate that behavior in a pivot table?