Have you been given report requirements to present a Current Month versus Previous Month metric based on user defined selections?
This report requirement recently came to our team and I thought I would share this example solution. This solution takes into account that the customer did not want to transpose their data. The requirement was for the report user to select an Analysis Month and Year then calculate the Previous Month, Difference and Difference Percent.
First, I had to determine how to prompt the report user for a month and year. Unfortunately, our parameters do not support date values so I had to use two numeric control objects and two numeric parameter data item. For more background on parameters see my blog here: Using parameters in SAS Visual Analytics.
I chose to use the Slider control objects:
Analysis Month Metric
Next, I need to calculate the Analysis Month metric based on the parameter selections. I decided to create a calculated data item so that this value could be used in a number of ways and in a variety of report objects. In this example, I used the Order Total metric from the Insight Toy data.
This logic is straight forward: if the year and month of the transaction date for the row equals the parameter selections then return the Order Total value, otherwise return 0.
Previous Month Metric
Next, I need to calculate the Previous Month metric based on the parameter selections. Again, I decided to create a calculated data item.
Here, the logic needs to take into account that if the user selects January then you will need to use December of the previous year; otherwise just take the previous month of the current year.
The next customer requirement included calculating the difference between the analysis month and the previous month:
And the final customer requirement was to calculate the Difference Percent as Difference/Previous Month. Since this calculation included a ratio, the best practice is to use an aggregated measure so that when the Difference Percent is aggregated in report objects you are properly aggregating the ratio.
Now that we have all of our calculations defined, it is always a good idea to verify we are getting the correct numbers.
To verify the Analysis Month and Previous Month metrics; use a crosstab and add the Year and Month of the transaction date. This is to ensure our logic is correct – be sure to test when the Analysis Month is equal to January!
For this customer request, they wanted to create a basic list table:
But let’s look at some other ways you can leverage these calculated data items. Here is one report using several crosstab objects stacked together with an interaction from the first crosstab filtering the lower two crosstabs.
This report puts more information on the screen and breaks the same metrics down across disparate group by categories.
One of my preferred visualizations to use for these two calculated metrics is the targeted bar chart. Many report requirements come in wanting to see how the metric compared to the previous month. For a visual that speaks a thousand words, this is the one to use.
This solution is not without its drawbacks. Since we did not transpose the data and created calculated data items to hold the Analysis Month and Previous Month values, those rows that do not fall into those two months contain zeros. Therefore; these calculated data items do not lend themselves to be used easily in line charts. However, if one of these report examples meets your needs, you now have a way to satisfy those requirements.