Question

Week Total with Table Calculations

  • 28 December 2017
  • 0 replies
  • 1222 views

Userlevel 2
  • Looker Staff
  • 25 replies

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}



0 replies

Be the first to reply!

Reply