How can I dynamically average over pivots

[conor (Conor O'Mara)]

Often you might want to pivot by a timeframe and get an average on the measure over the groupings within the timeframes. For example you might want to get the weekly active users for the last 4 weeks. Then average this value on a weekly basis. Here’s a workflow to do that in Omni so that the average updates when you change the filter to different numbers of weeks.

Steps

1. Select your dimension, measure and pivot on a timeframe/dimension of your choice. Here I’ve got order status, measure is order items count and I’ve pivoted on weeks.
2. Add a new column and move it outside the pivot. Then insert the following into the calculation outside of the pivot =AVERAGE(PIVOTOFFSET(C1, 0, 0, 1, 5000)). Updating the C1 reference to the column you want to average over in your pivot. This will take the average up to 5000 cols in your pivot.
   
   
3. Add your filter and see how the calculation updates when you change the values within the filter (see Loom).