pivot table total mode, workaround with aggr and avg problem for LY

[#1]

Hello,

As far as I know in pivot table its not possible to choose different total modes for partial sums so I've been trying to come out with some sort of workaround for that. Basically instead of taking sum in total mode, I need to figure out how to get the average.

There are two columns: basket size TY and basket size LY.

For basket size TY I got it working with this expression:

=avg(aggr(sum(D_SPT), Store, Date)).

I can't get LY working, this is one of the expressions that Ive tried

=avg(aggr(sum({$<D_Weekend = {"$(=date(D_Weekend-364))"}>} D_SPT), Store, Date))

I attached my report.

Thank you

[#2 · Accepted solution]

I changed the expression to:

=avg({<D_Weekend = {"$(=date(D_Weekend-364))"}>}
 aggr(sum({<D_Weekend = {"$(=date(D_Weekend-364))"}>}D_SPT), Store, Date))

You are doing two aggregates in that expression. If you only put the Set Analysis in the interior, it won't work, because the exterior function would still be operating based on the selection, which is the 2010 date. If you put the Set Analysis in both functions, they both will properly evaluate for the selected date - 364.

[#3]

This makes sense, thank you!