Data offsetting
1. Data offsetting
In this demo, we’re going to learn how to offset time-based data. Using offset data has lots of applications; here, we’ll use it in the context of transaction orders. In your exercises, you’ll use it to order touches of individuals to analyze their marketing journeys. Typically, when stored in a tabular manner like Excel, time-based data appears as a list of different transactions, as we can see in this example data set. Therefore, if I wanted to see what transaction proceeded or followed another transaction - for example, who made a transfer right before Daniel, I’d need to look at another row. But what if I wanted to look at this information at the same time as other data? For example, what if I wanted to see a transaction’s value as well as the values of the prior and next transactions all in the same row? That wouldn’t be possible with this data structure, unless we make use of data offsets. Data offsets are a way of “pulling” data from one row into another. You may have used something similar in Excel or SQL. Today we’ll see how they work in tableau using the LOOKUP function. Let’s start by building a calculated field for the prior value, for each transaction. We can build a calculated field using LOOKUP. The first argument that we'll use is the value we want to “pull” from a previous row, in this case, the sum of the value of the transaction. The second argument is an offset that states the number of rows to skip. Since I want the prior row here, I’ll use -1. I’ll then name the calculated field “prior transaction value” and add it to our table. Re-arranging so that we have things as we did before, we'll see that it looks pretty good. But how did Tableau know how to do that? Let’s take a closer look. I’m going to select the drop down menu and click ‘edit table calculation’. You’ll notice the fields from our table are automatically specified in the list under “Specific dimensions”, and in this case, they were automatically selected. But what happens if we select just Day of Transaction Date? Ah: Tableau re-organizes the data to be by day, and creates dummy rows so that every user gets the previous value stored at the next date in the data set. So we'll see there's lots of empty values here, where they originally didn't exist in the data set. This isn't quite what we're looking for. What if we add user? Hmmm: this isn't quite it either. Tableau doesn't seem to be looking the right way. What if we add transaction ID? Yes: sure enough, when we add transaction ID, we're getting the values that we want to see, because we've specified that Tableau should be looking across all three of these dimensions when getting the previous values. So we can see here that the values are pulling through correctly. Ok. Now let’s do a similar calculation but find the following purchase value. Let's duplicate our prior, and this time, we’ll use a lookup function again, but an offset value of 1. Quickly change the name. When we drag this to our table, we see that Tableau replicates the same table calculation settings as Prior Purchase Value. A quick visual check shows that the new calculated field is functioning correctly. Now that we have the prior and next value for a given transaction in the same row, we can do calculations that involve the three values. For example, we can calculate the rolling average of three transactions at a time. Here, I’ll simply add the three values, divide their sum by 3, and rename the field Rolling average Now when I drag the field into the table, I can clearly see a dip in October, and can quantify the average value of that drop very quickly. This is just one way to use the Lookup function in Tableau. If this were a sample of data from a series of banking transactions, we can now more clearly see a significant drop in the value of transactions in October, as a rolling average of 3 transactions is a more stable metric to analyze change in comparison to individual values. In upcoming exercises, you’ll use them on web traffic data. Good luck!2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.