There are many hidden talents within Web Intelligence that can assist with massaging your data and bringing out relationships within your numbers without ever having to export your data outside to another tool, such as Microsoft Excel. One of those underutilized functions is PREVIOUS(). It simply returns the previous value of an object.
While Web Intelligence does not include any built in functions for finance, accounting, or statistics, many calculations can be created using some of these native functions such as PREVIOUS().
Let’s look at this data set below from our trusted e-Fashion Universe.
We have a simple horizontal table broken out by Month, Sales Revenue, and Quantity Sold. What if we wanted to see how the Quantity sold measure varied from month 1 to Month 12? Could we do that with just the PREVIOUS() function? Let’s give it a shot.
First, we will create a new column and name it “Previous Qty”. Next, we will insert the following formula under it =Previous([Quantity sold]).
As you can see in our new measure, the first value is blank as it doesn’t exist but every subsequent value is the previous value to the column with the measure it is referencing. To make the numbers look a bit nicer, I like to add a 0 to the previous function =Previous([Quantity sold])+0.
Next, let’s create a new column to the right of “Previous Qty” and let’s call it “Variance”. We will assign “Variance” the following calculation:
=[Quantity sold]-Previous([Quantity sold]).
We can now see that the Variance is the difference between month to month of the Quantity sold. However, since the first month is serving as our baseline, rather than having it say 24,805, we really want it to say 0. So, we will slightly modify our variance calculation to do the following:
=if [Month]=1 then 0 else [Quantity sold]-Previous([Quantity sold])
Now, our table makes more sense.
We can also graph this out to see how the quantity changes month to month.
I have found in the past that the PREVIOUS() function works best when dealing with time series measures as it allows for a logical path for comparing numbers. There is a lot of hidden potential within these type of functions inside of Web Intelligence that can be used for more industry specific calculations in reports.