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 one 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 has a negative one offset, so it captures the value from the previous row. There isn't a value for October, since it's already the first row of your 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 get September's total revenue. As you can imagine, the sort order of your data is critical here. If you're sorting your data from newest to oldest, as seen here, you need to do a positive offset to get the previous month's data. If you're 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 the column to the left or to the right when you have a pivot table. In this example, there's 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? How did February 2013 compare to February 2012? To determine this, the last year revenue table calculation looks to the minus 1 column or one pivot column to the left, and pulls the total revenue from there. It's 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 a 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 that you want. In this example, the table calculation using offset list references three rows back or minus 3 rows, and starting at that point gets the order account for next three 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 their 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 seven 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, another table calculation to determine something new, like the mean of the 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. There are three main types of offset functions. Regular offset for referencing rows higher or lower in a column, offset lists for referencing values in a column to the left or the right, and pivot offset for referencing multiple rows, and combining their values into one new value. With these 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.
