How to create a running/rolling anything function (sum, mean...) down a column or across a pivot row

Knowledge Drop

Last tested: Jan 21, 2019
 

You can do this using a custom table calculation formula.

Down a column:

offset_list(

${view_name.field_name},

-1*row()+1,

row()

)

Across a pivot row:

pivot_offset_list(

${view_name.field_name},

-1*pivot_column()+1,

pivot_column()

)

Just wrap your aggregation function around one of these formulas, and you're good to go!

Examples:

Running standard deviation down a column (yes, you can use this with any aggregation function available for table calculations!)

stddev_samp(

offset_list(

${view_name.field_name},

-1*row()+1,

row()

)

)

Running total across a pivoted row:

sum(

pivot_offset_list(

${order_items.count},

-1*pivot_column()+1,

pivot_column()

)

)

Screen Shot 2018-08-24 at 6.17.27 PM.png

This content is subject to limited support.                

Version history
Last update:
‎04-05-2021 09:03 AM
Updated by: