Community

Week Total with Table Calculations

In many cases it may be more suitable to create a fact table that rolls up values by week however you may find yourself in a situation where you want or need to do this in an explore using table calculations.

In this example we will begin from our sample orders explore with created_at_date, created_at_week, and count selected

If we want to see the total count by week as well, we can use:

  1. if( ) logic and offset( ) to find the end of a given week (when the next week value is different
  2. offset_list( ) to get the values from that row and the previous 6
  3. and finally take the sum of that list
if(
  NOT(${orders.created_week} = offset(${orders.created_week},1)),
    sum(offset_list(${orders.count},-6,7)),0)

This leaves us with a value of 0 throughout the week, and finally on the last day of a given week will be the total value, similar to a subtotal by week of the range shown.

#Using Week Total to make further Calculations

Though seeing the week total is useful visually, in order to perform calculations like how much each day’s count is as a percent of that week’s total, we will need to have the week total displayed on each row of the week.

To do this we can again rely on offset_list( ) to grab the values from the current rows and the next six, and take a maximum of from that list to get the week total. This works because a window of 7 days will never return totals from two different weeks and the values in the week total column can only be 0 or the week total.

max(offset_list(${total_calc}, 0, 7))

And with this we can do things like calculate the percent of the week’s total that each day’s count is with this simple division and the percent format.

${orders.count}/${week_total}

1 Like