Knowledge Drop

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

  • 5 April 2021
  • 0 replies
  • 787 views

Userlevel 5

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.                

 


0 replies

Be the first to reply!

Reply