Offset functions are a subset of table calculation functions. They allow you to programmatically reference values from other rows or columns in your query results to calculate new values. There are three main types of offset functions: regular offset(), offset_list(), and pivot_offset(). A regular offset() function is used when you want to reference a value from a higher or lower row of your results. Note that changing the order of the rows by sorting your results can turn the previous row into the subsequent row, and vice versa. In this example, there are results for a series of months in descending order—October, September, and so on—with the Total Revenue for each month. The Previous Month Total is a table calculation that offsets the Total Revenue column by one. The calculation starts with October, which is in row 1. Then, it adds 1 to its row number, and captures the Total Revenue from row 2, which is September. The Subsequent Month Total is a table calculation that offsets in the other direction; it does a negative-one offset, so it captures the value from the previous row. There isn’t a value for October, since that is already the first row of our results set. For September, it goes back from row 2 to row 1, and gets October’s Total Revenue. For August, it goes back from row 3 to row 2, and gets September’s Total Revenue. As you can imagine, the sort order of your data is critical here. If you are sorting your data from newest to oldest, as seen here, you need to do a positive offset to get the previous month’s data. But if you are sorting from oldest to newest, you need to do a negative offset to get the previous month’s data. With these new values, you can create yet another table calculation to calculate the percent difference from one month to the next, to see if business is improving or not. The pivot_offset() function is used to reference values from a column to the left or the right when you have a pivot table. In this example, there is a Created Month Number dimension with a pivot on Created Year. From these results, maybe you want to know: how did revenue from January 2013 compare against revenue from January 2012? Or how did February 2013 compare to February 2012? To determine this, the Last Year Revenue table calculation looks to the -1 column, or one pivot column to the left, and pulls the Total Revenue from there. It is blank for 2012, since that is the very first pivot column in the results. The sort order matters here, too, to determine whether you should be offsetting by a positive or negative value. Once again, you could create yet another table calculation to calculate the percent difference from one year to the next, to see if business is improving or not. The offset_list() function moves up or down a column of rows defined by a first provided value, and then grabs another number of rows’ worth of data defined by a second provided value. The two numbers can be the same or different, depending on the results you want. In this example, the table calculation using offset_list references 3 rows back, or -3 rows, and starting at that point, gets the order count for the next 3 rows. This means the first row where it’s possible to get a full string of values is now row 4, where we can go back to row 1, and starting there, grab the three values of 391, 359, and 388 for rows 1, 2, and 3. While this type of offset is not used as often as the first two types, it’s perfect for calculating rolling averages. You may want to know, what is the rolling average number of users over the past 7 days? Or what is the rolling average number of purchases over the past 30 days? That’s where it might be useful to create one table calculation with the offset_list(), and another table calculation to determine something new, like the mean of that resulting list. In summary, offsets are a subset of table calculation functions and allow you to programmatically reference values from other rows or columns in your query results to calculate new values. With new values, you can create even more table calculations to calculate new metrics such as the percent difference from one month to the next, one year to the next, or even a rolling average. Very powerful features!
