How to filter on multiple date periods based on a reference date in SAS Visual Analytics

[#1]

In my previous article, Date parameters in SAS Visual Analytics, I presented a solution to pass dates as parameters. I mentioned a use case as an example where a reference month is selected and the report shows the past three months of data, up to the selected reference month. Independently if you need the past few months, or same month last year, or something else that is not exactly the selected month, the problems are similar - as well as the solution. In this article, I’ll will explain how this can be done.

Report filters, section filters, and filters via interactions are able to filter on the selected value only. If you want to select one month of reference and return extra months, in addition to the selected month, you will need more than a direct filter.

The solution proposed here uses parameter and calculated items to support advanced filters.

Note: this technique can be applied to whatever time period you want. In this example, we will be interested on the selected month, previous month, and same month last year.

Solution Explained:

Suppose your original date column is called Transaction Date, you will start by leveraging the technique presented previously in Date parameters in SAS Visual Analytics, which explains how to use dates parameters in control objects, to select the reference month from a Drop-Down List control.

Instead of repeating the steps here, I will assume that you already have a Drop-Down List loaded with months derived from Transaction Date and formatted as YYYY-MM … and the selected month is captured in a parameter called Selected YYYY-MM. That parameter is a character element, but I showed you how to derive a calculated item of type date from that parameter, that we called Selected Month Year. Again, if you don’t know or don’t remember how that is done, please refer to the previous article. For those of you that only need a quick refresh, please check out the diagram below that summarizes the steps:

1. Date column Transaction Date is used to create a character calculated item with the date formatted as YYYY-MM.
2. Calculated item is called YYYY-MM.
3. Calculated item YYYY-MM is used to populate the Drop-Down List category role. Parameter Selected YYYY-MM is also associated with this control object.
4. Selected drop-down value is assigned to the parameter.
5. Parameter is used in another calculated item to parse the character value back into a date.
6. This calculated item is called Selected Month Year.
7. Selected Month Year, formatted as MMMYYYY, is displayed on a List Table for visualization

At the end of the steps above, Selected Month Year holds the reference month that you will use to filter the other months. As explained in the other article, Selected Month Year is a date, where the day portion of this date is set to 1. For example if you select 2013-10 from the drop down object in the report, the equivalent date will be October 1^st, 2013. This is important, because if you want to compare the Selected Month Year with the Transaction Date from your data source table, which could contain any days of the month, you will need to standardize those transaction dates by mapping them to the first of the month before performing any comparison. Let’s call this standardized transaction date Trans Month Year, which is calculated with the expression below, that uses the Month() and Year() functions to decompose the date and the DateFromMDY() function to reassemble it with day equals to 1:

Note: functions Month(), Year(), and DateFromMDY() are found under the Date and Time group of operators in the expression window.

In this particular example, you want all the data that satisfies any the following:

• Trans Month Year= Selected Month Year                         --> this is equivalent to the reference month
• Trans Month Year= Selected Month Year – 1 month        --> this is equivalent to the previous month
• Trans Month Year= Selected Month Year – 1 year           --> this is equivalent to the same month last year

Deriving the same month last year is relatively easy because you only need to subtract 1 from the year, and SAS Visual Analytics offers functions that you can use to accomplish that. Bbut getting the previous month requires additional logic (use of IF-THEN-ELSE) depending on the month being January or not. In other words, if the month is January and you subtract 1 month, the month becomes 12 and you need to subtract 1 from the year.

You don’t have to do this, but there are some advantages if you rewrite the equations above to isolate Selected Month Year at one side of the equal sign:

• Selected Month Year = Trans Month Year                       --> this is equivalent to the reference month
• Selected Month Year = Trans Month Year + 1 month      --> this is equivalent to the previous month
• Selected Month Year = Trans Month Year + 1 year         --> this is equivalent to the same month last year

The advantages of rewriting the equations as suggested are the following:

1. It makes it easier to define the filter expression that you need (no IF-THEN-ELSE logic to detect beyond the year boundaries). This will get clear when you see the expression to add one month to Trans Month Year.
2. You can calculate the entire right hand side of the equation upfront, in a data prep phase if you want.

Let me explain. Starting with #2, because Selected Month Year, the reference month, is chosen by the report consumer when viewing the report, you cannot pre-calculate what the previous month or same month last year will be. With the rewritten equations, instead of subtracting one month or one year from an unknown value, you are adding one month and one year to a known value, and this can be done at any time, including a data prep phase. Even though this is possible to be done ahead of time, I will show you how those elements are calculated in SAS Visual Analytics. I will go back to #1 in a while.

Subtracting or adding one year to a date is very simple. First, you have to decompose the date into month, day, and year components, then subtract or add one to the year, and finally reassemble the date, similarly to what you did to calculate Trans Month Year. The following expression adds one year to the Transaction Date, creating a calculated item called Trans Month Year+1:

Regarding #1, as said before, adding or subtracting months can get a little tricky when the result goes beyond the year boundaries. You could use IF-THEN-ELSE logic in the expression to account for situations like that, but the expression below introduces a different way of handling this, if you are adding one month to an existing date for example. This is actually how you can define the calculated item Trans Month+1 Year that computes Trans Month Year + 1 month:

Observe that the overall template is the same one used previously for the other two calculated items, but it also uses two additional functions: Mod() and Trunc(). Function Mod() is used to compute the month and returns the remainder from integer division, so a month number Mod 12 is an integer between 0 and 11. All months between January (month number = 1) and November (month number = 11) will return their respective month number, but December will return 0. In any of those cases, adding 1 to the Mod will give you the correct next month. Function Trunc() returns the integer part of a number, so when you divide the month number by 12, it will be 1 for the month of December and 0 for all others, and this will do the trick that allows you to correctly adjust the value of the year on the calculated date.

Note: both Mod() and Trunc() functions are found under the Numeric (advanced) group of operators in the expression window.

If you want to simply use the IF-THEN-ELSE approach, this is how the expression to add one month would look like:

Of course, the expression above could be simplified and maybe optimized little bit, but this is just easier to understand. Besides, this is a generic expression that could be used to add from 1 to 12 months: just replace the highlighted 1’s with the number of months you want to add. An even more generic expression could be used to add as many months as you need, as showed below, where M is the number of months to be added (M>0):

Back to the original problem, you now have all the elements to filter the report and display only the reference month (Selected Month Year = Trans Month Year), the previous month (Selected Month Year = Trans Month+1 Year), and the same month last year (Selected Month Year = Trans Month Year+1). All you need to do is define an Advanced Filter to combine all three expressions with the OR operator.

In the figure above, October 2013 was selected as the reference month. The same month value is highlighted on the table to the left for the three auxiliary columns you created. As a result of applying the filter, only data for the desired Trans Month Year values of Oct2013 (selected), Sep2013 (previous), and Oct2012 (same month last year) were displayed on the right. This is a simple visualization it is probably not what you want in your report, but as you can imagine, this filter can be used with any visualization, keeping the context within the subset of interest.

Just to recap, this is what we did:

1. Introduced the challenges of filtering information based on a reference value (used month as an example).
2. Quickly reviewed how to use dates as parameters.
3. Defined the filters in terms of equations.
4. Rewrote the equations to simplify and optimize the solution.
5. Explored a few different ways to implement the equations with pre-defined (and maybe not so common) functions.
6. Delivered the final solution, where a reference month is selected and additional months are returned for comparison.

Parameters are really powerful and I cannot imagine reports without them. They become even more powerful when brought into expressions and calculations that leverage SAS Visual Analytics functions. This is a perfect combination that allows you to take your SAS Visual Analytics reporting experience to the next level.