Custom Total Expresions in a Pivot Table

[alex7606]

 Good day community,

     I have a simple pivot table with customer on the row and months accross the top, the metric used is quantity of shipments; then I calculate the difference of a given month vs the previous month in percentage. So far so good. Then the requirement I have from my user is that I need to show the last 3 Months average of that % difference, last 6 Months and last 12 Months; I could use expresion formulas like this:

=(((Sum({$<PeriodOrder={12}>} ShipCount) - Sum({$<PeriodOrder={11}>} ShipCount)) / Sum({$<PeriodOrder={11}>} ShipCount))
+((Sum({$<PeriodOrder={11}>} ShipCount) - Sum({$<PeriodOrder={10}>} ShipCount)) / Sum({$<PeriodOrder={10}>} ShipCount))
+((Sum({$<PeriodOrder={10}>} ShipCount) - Sum({$<PeriodOrder={9}>} ShipCount)) / Sum({$<PeriodOrder={9}>} ShipCount))
) / 3

But I do not know how to add the 3 custom total avg formulas on the total section of the pivot table without generating dummy columns in the body of the pivot table, do you know if is possible to do what my user wants?

Currently what I am doing is to generate a second table with just the expresions I mention and I am using a slider to try to synch the scroll but is not a nice solution, I hope there is a better one.

Thanks in advance for your help.

[chrismarlow]

Hi,

Not sure this is going to help but solutions I have seen to mixing this kind of summary field with pivoted data have generally involved adding expressions (with set analysis) for all the pivoted items (then removing the dimension that pivots, in effect not making use of the fact it pivots).

This has relied on the user not really needing a pivot table as such (with a variable number of columns), it just being that is how they did it in Excel (so they assume they need a pivot here).

If for example they wanted to see a whole year you end up with a lot of expressions (27);

1) Sum({Month1}ShipCount)

2) Average based on Month1 (to month 0),

...

23) Sum({Month12}ShipCount)

24) Average based on Month12(to month 11)

25-27) Your average of average columns

Which is long winded, but at least everything stays lined up. You could make some of the expressions conditional, to hide columns when filtering on dates, which is what a pivot table would do if they actually want that.

Cheers,

Chris.