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

Problem summing an aggregated average in pivot table

mabryanjr — Mon, 16 Aug 2021 20:58:56 GMT

I have a pivot table that calculates average weekly values at a locations within various regions, and I need those average values to sum up to their total within the region.

The expression used to generate the weekly average per location is:

Avg({<[Event Date] ={$(vR5Wk)},Event={'Event A'}>} Aggr(Sum({<[Event Date] ={$(vR5Wk)},Event={'Event A'}>}EventCount),[Event Date], Region, Location))

vR5Wk is a variable to capture the Event Dates in the most recent 5 weeks

the events are tied to region and location by association to another table via their key

I have tried to sum the aggregation of the above measure, but am not getting the desired results. Any assistance or thoughts are much appreciated. Thanks!

Re: Problem summing an aggregated average in pivot table

mabryanjr — Mon, 16 Aug 2021 21:00:38 GMT

To clarify...as you can see in the QS Pivot Table Extract, the totals are an average of the locations in each region. I need to get the sum.

Re: Problem summing an aggregated average in pivot table

Kushal_Chawda — Mon, 16 Aug 2021 21:21:43 GMT

@mabryanjr try below

=if(dimensionality()=1, sum({<[Event Date] ={$(vR5Wk)},Event={'Event A'}>} Aggr(Sum({<[Event Date] ={$(vR5Wk)},Event={'Event A'}>}EventCount),[Event Date], Region, Location)), Avg({<[Event Date] ={$(vR5Wk)},Event={'Event A'}>} Aggr(Sum({<[Event Date] ={$(vR5Wk)},Event={'Event A'}>}EventCount),[Event Date], Region, Location)))

or bit optimized

=if(dimensionality()=1, Sum({<[Event Date] ={$(vR5Wk)},Event={'Event A'}>}EventCount), Avg({<[Event Date] ={$(vR5Wk)},Event={'Event A'}>} Aggr(Sum({<[Event Date] ={$(vR5Wk)},Event={'Event A'}>}EventCount),[Event Date], Region, Location)))

Re: Problem summing an aggregated average in pivot table

mabryanjr — Mon, 16 Aug 2021 21:40:12 GMT

Thanks, that is what I needed!