LinkedIn
Twitter
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:
- if( ) logic and offset( ) to find the end of a given week (when the next week value is different
- offset_list( ) to get the values from that row and the previous 6
- 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}
