Creating a Running Total Across Rows Redux: Using pivot_offset_list


(jonathan_tao) #1

In an earlier post, Lindsey provided a great way of leveraging the pivot_offset function of table calcs to calculate a running total across rows when using a pivot. Today, I’m going to revisit that calculation while using a new table calc function: pivot_offset_list.

In version 3.50, pivot_offset_list was released. By simply adding an extra parameter, you can now return a List of pivoted values instead of singular one. Since existing aggregation operators support Lists as input, you can perform elegant running row totals with very simple code!

Example 1: Day of Month Running Row Total

In this example, I will show you the table calculation needed to obtain a running row total based on a day_of_month pivot.

sum(pivot_offset_list(${order_items.count},(-${order_items.created_day_of_month} + 1),${order_items.created_day_of_month}))

I am taking advantage of the fact that the day_of_month pivoted dimension is providing the consecutive values I can leverage in a table calc. In the second argument, I specify the offset, which is simply a function of the current day_of_month value plus 1 (I negate it to go to the left of my value in the table) Next, I simply call upon that same day_of_month value to specify the range of the List.

Other consecutive datetime dimensions would work great here as well!


(Eric Nelson) #2

Great info, thanks! I found that this general pattern was useful for tables with a single pivot:

sum(pivot_offset_list(${view.measure},1-pivot_column(), pivot_column()))

This was useful for getting every value in the columns preceding + including the current column.