VA Report Example: Number of Days Profitable for the last 30 days
A report requirement came to me that showcases the new SAS Visual Analytics 8.3 Aggregated Data Source and Join Tables features nicely. In this article, I will focus on using these features to solve this business question:
How many days of the last 30 days has the company been profitable?
Please refer to my previous articles if you want more detail about aggregated data sources or join tables.
This was a fun exercise to develop. Not only can we display the number of days of the last 30 days that have been profitable, but we can show the number of days that have been profitable for the available data or even use a prompt to ask the user for the number of days window.
Let’s dive in. Here is the resulting dashboard I mocked up. Granted I’m not using the measure Profit but the measure Order Total. Essentially, I am still interested in the number of days that there was an increase in Order Total from the previous day. For every instance there is a positive difference, we will count that as 1 day that is “profitable” or positive. As you can see from this example, I have identified the number of “positive” days for the last 30 days as well as the number of “positive” days for the data by selected year.
First you might be wondering, why do I need to use these new features? It is due to the behavior of aggregated measures. Aggregated measures will evaluate the data exactly as the expression defines, even though it may not always be how we intend. It reminds me of that expression “to obey the spirit of the law versus the letter of the law.” It’s a computer, it will do exactly as it’s told. Let’s take a look.
Inspect the top objects versus the bottom objects. You can see that the detail data is the same but the main differences occur with the Number of Days Profitable at the top versus the Profit Count at the bottom. The bottom numbers are returning the expected results.
You can see that the expression to count the number of profitable days is the same. If the difference between current period and the previous period is greater than zero, return 1, else return 0. So why does the bottom method return the expected results? It’s because of the aggregated measure’s relative period evaluation. Instead of creating a value for every row of data like the simple subtraction does, the aggregated measure is being dynamically calculated at the aggregated level.
Look at the top list table. The Total of Order Total (Difference from period period) is missing. Its value is missing not because it “can’t” sum up, but because the aggregated measure periodic operation has no period transaction date to perform the comparison. And therefore, when Number of Days Profitable goes to evaluate that value, is missing greater than 1? No, then assign the value zero. This is where the “letter of the law” is being followed, i.e. the expression is behaving as designed but not what we want.
How can we work around this? We can create a value for every row of data that can then be evaluated using a calculated data item and not an aggregated measure. The distinction between the two is that a calculated data item is evaluated for every row of the data where an aggregated measure is evaluated dynamically depending on the object’s role assignments.
We need to be able to do a simple subtraction instead of using the aggregate measure relative period operator. To do this, we will need to create two aggregated data sources and join them together.
The first aggregated data source will represent the Current Period. From the Data pane with your original data source selected use the Drop-down menu and select New aggregated data source....
At the top, be sure to add _CurrentPeriod as the suffix to the name of the aggregated data source. Then select the Transaction Date and Order Total as the columns to keep.
When the aggregated data source resolves, be sure to rename the Order Total data item so that it represents the Current Period.
Now we need to create an aggregated data source for the Previous Period using the same original data source. Let’s look at how we need to shift the data. We will use the date from the original data source, Transaction Date and then add 1 to it. This is because, given the way the VA Join Table feature behaves, it will join where the selected column values are equal. Therefore, we need the date values for Current Period and Previous Period to match, i.e. be equal, in order to join.
What’s the catch? Or, what is the draw back to this technique? It means that you cannot use the Previous Period aggregated data source as representative of the actual Previous Period date and value combination. The Previous Period will only be a valid value to use in the final Join result and when paired with the Current Period.
So, we will need to create this fake previous period date to use in our join. In the original data source, we will need to create a new calculated item. Here is the expression, notice how you will need to use the Treat As operator to manipulate the date data item. Remember, we are taking the original date, which is at the day level and adding 1 day to it.
Now we can create the _PreviousPeriod aggregated data source. Be sure to select the newly created date!
When the aggregated data source resolves, be sure to rename Order Total measure to represent the Previous Period.
Now we are ready to join our two aggregated data sources together. Select the aggregated data source for Current Period. Then from the Data pane Drop-down menu select New data source join....
Your Data Source Join definition should look like this. The join condition should be where Current Period’s Transaction Date equals Previous Period’s TransactionDate_PP.
For the selected columns, I brought in both transaction dates and both order total measures.
Once the join resolves, we need to create two calculated data items. This is what we have been after the entire time. The ability to use two columns to create a simple subtraction to replace the aggregated measure periodic expression. That way our IF/THEN/ELSE will be evaluated for every row of the data and can be summed.
The first calculated data item to create is the Order Total Profit.
Next, create Profit Count.
Now we get the behavior we desire. This was all about being able to get the number of days that have been “profitable” or in my case a positive difference from the previous period over the last 30 days.
Take another look at the lower objects that are behaving as we like. In the List Table object, we can see how the current period and previous period numbers are accurate. Even when there is gap in time the calculations pick up as expected. We can see that the data goes from January 4th to January 7th and that is why there are some missing values in the data. If you wanted to treat these values differently, then you would create a new calculated data item to replace the missing value with the one you desired.
For the Crosstab object, I created a hierarchy using Transaction Year and Transaction Month/Year to get a by-month breakdown count of profitable days.
Let’s get back to the business question and the dashboard I mocked up. How many days, in the last 30 days, have we been profitable?
I decided to use a Key Value object, and I assigned a Rank to it so that it only aggregates the Profit Count for the last 30 days. This article can help if you are interested in the Key Value Object in SAS Visual Analytics and this article provides a step-by-step guide on how to configure the Rank using a date.
To display the count for the entirety of the data, 79 in the below report, just leave off the rank.
Just remember that you cannot not use the aggregated data source labeled Previous Period as true date and value pairs since we used this technique to shift the data for the data join.
