Data Tracking and Useful Functions
Lately a number of my users have shown an interest in data tracking, but when they go to use it they find it doesn't always work as they expect. So let's break down what it is for, and then go through some really useful functions you can use with data tracking, such as RefValue(), Previous(), and If().
First of all, what is data tracking? Data tracking is a Web Intelligence feature that allows you to see the differences between two versions of your query results. For example, if you run a query daily you can compare today's results to the prior day's results. The previous data set is called the "reference set". Web Intelligence keeps the previous results as well as the current results, and compares the two. Then it shows you the differences with lots of pretty colors.
You can find data tracking in the Analysis toolbox in 4.x. When you turn data tracking on you have the option to choose the data from the previous refresh as the reference set or the data at the point you turn tracking on as the reference set. Once you turn tracking on, Web Intelligence will keep the reference data set each time you refresh, and compare the results.
For example, here is a simple report. When you decide you want to track data you simply click the "Track" button.
Once you turn tracking on and refresh the query you'll see measures that increased or decreased are shaded. New records are in blue and deleted records have a strikethrough, as shown below. Note that you can adjust the data tracking formats under the data tracking options.
If you want to see the differences between the current amount and the reference set amount (the prior amount) you can create a variable that takes advantage of the RefValue() function, which is intended for just that purpose. **Be sure whenever you use RefValue() function the variable is defined as a measure or it often doesn't work right.
If you add that variable to the table you'll see you get the prior Trans Amt value.
So, that's the straightforward data tracking example. Let's dive a bit deeper into some problems you'll see with data tracking.
Measures are easy to track - they show you the differences with colors. But if you want to see when a dimension value changes it isn't as simple because a dimension value change is treated as an entirely different row. For example, in the screenshot below Esther Frazier's Pymnt Due Dt value was 2/20/2015 and changed to 4/20/215, which is why one row shows as deleted and the other row shows as inserted.
One solution is to change the object(s) in the universe from a dimension to a measure. Even if it is a name, date, or some other value that really shouldn't be defined as a measure I've sometimes duplicated the object and defined it as a measure just for use with tracking changes. Another option is to take advantage of functions to show the differences.
In the example below, the RefValue Function Example column shows the prior value, but with dimensions you'll notice the value is always equal to the current value. Except in one case - new rows. New rows receive a null since there was no prior value. That makes the RefValue() useful for creating data such as the Filter Flag column, which uses an If() statement to identify (null) new rows. You could then filter on the 1 if you want only new rows. The Previous Function Example column shows you the previous row's Pymnt Due Dt, which allows you to see the prior value on the same row as the current value. Cool, huh?
Here are some screenshots for each variable, in case you'd like to see.
RefValue Function Example
Filter Flag Example
Previous Function Example
(the way this function is written you get the Pymnt Due Dt from the previous row as long as the previous row has the same Acct Num value)
Gotta love functions.