PERSON: Offset functions are a subset of table calculation functions. They allow you to programatically 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 lists 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 the table calculation that offsets the total revenue column by one. The calculation starts with October, which is in row one, then it adds one to its row number and captures the total revenue from row two, which is September. The subsequent month total is the 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 it's already the first row of your result set. For September, it goes back from row two to row one and gets October's total revenue. For August, it goes back from row three to row two and gets 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 is a created month number to mention 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 one 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 three rows, and starting at that point gets the order count for next three rows. This means the first row where it's possible to get a full string of values is now row four, where we can go back to row one and starting there grab the three values of 391, 359 and 388 for rows one, two and three. 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, 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 programatically 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.
