Pivot table with averages - problem with the total

[#1]

I have a Pivot table with 4 dimensions:

Product (Row)

Branch (Row)

SalesMonth (Column)

Stage (Column)

and 1 expression

sum(Average) as Average

The aim is to show the average length of time it takes for a Product to pass through each stage from Sale to Installation depending on when the Sale was made. For example a sale made in Branch 1 for Product 1 in Month 1 takes 50 days whereas if the sale was made in Month 2 it takes 60 days to install.

The Average length of time in each stage is calculated within the loader script by SalesMonth, Branch and Product.

When Product and Branch are expanded the chart is fine but they also want to see the overall average for each product across all of the branches - this bit doesn't work as it just gives a TOTAL of all of the Averages. What I need is an AVERAGE of all of the Averages.

I can't really give an example due to the sensitivity of the data but if you need any more information I'll try my best.

[swuehl · Accepted solution]

sum(Average)

is your expression? Not sure if I would sum averages, but you will know.

You could try using advanved aggregation to get an average of your table values instead of an expression total.

=avg( aggr( sum(Average) , Product,Branch,SalesMonth, Stage))

Please check the Help for some information about using aggr() function in the context of sum-of-rows (which in your case would be better called avg-of-rows).

If the level of aggregation is not correct, you could probably succeed with adding a total qualifier or refining the aggr() dimension list / using two embedded aggr() functions. If you are running into problems, could you post some mockup data together with your required table outcome?

Hope this helps,

Stefan

[#3]

Thank-you that was absolutely spot on. I was considering doing 2 charts.

I've never had to really use aggr before now but this works so really pleased.