Use the latest date in your VA report

5 Likes

Using the latest date in your report can be helpful to your report viewers so that they can easily and quickly identify how recent the data is for the report. You can display this value as a footnote, report or object title, or you can even use the latest date to drive calculations for moving windows of interest, such as displaying the last 30 days of activity.

Once you’ve mastered the technique of obtaining the latest date, the possibilities of enhancing reports with this value become endless. In this article, I will walk through how to find the latest date in your data, assign that value to a parameter, and cover ways in which you can use this value. Don’t miss the list at the end with several other blogs and articles about using dates in your reports.

Find the latest date technique

As a reminder, a SAS date is the number of days since January 1, 1960. Therefore, when you see a typical date, such as Month Day, Year or MM/DD/YYYY these are actually SAS formats being applied to the number of days since January 1, 1960. See the below table as an example.

To get the latest date, we need to get the maximum value. We can use the Ranks functionality to help us out. Here we will add a rank to the List Table to show the top count of 1, i.e. the maximum value, by the SAS date as a number.

If you are wondering, how you get the SAS date as a number? Let me remind you. Create a new Calculated item…. Then use the TreatAs operator to convert the date to a number.

Once you have your date represented as a number we need to do one more thing. We need to make this numeric non-additive. If your data has multiple rows per date value, we don’t want the aggregation of those rows to change the value of the date or the numeric that is representing that date.

To make that change, open the Data pane. Find the newly created date as numeric data item, select the chevron arrows to expand the properties, then change the Aggregation to either minimum or maximum. Both are non-additive and will suit our purposes.

Okay, now we have our latest date, but do we really want to use this List Table as our footer or report title? Probably not, so let’s look at how we can use this same technique for other examples using this technique.

Technique Summary

Create a new calculated item using the TreatAs function to get the numeric version of your date.
Change the aggregation of this numeric date to be non-additive, either minimum or maximum.
Add the date to your desired object.
Add a Rank to the object for a top count of 1 by the numeric version of your date.

Latest Date as Button Bar Header

In this example, I will use a Button Bar object to serve as my report header. As I described before, the first step in the technique is to create a new calculated item using the TreatAs function to get the numeric version of the date we will use in the report.

Remember to change the aggregation to be non-additive for this new calculated data item.

Now add a Button Bar object to your report and assign the date to the Category Data Role. Then apply the Rank technique just as I outlined above.

Here is what the example report looks like:

You can see I added a Text Box with left aligned text next to the button bar to polish off the header. You could have used the Button Bar’s Title Option to add text if you prefer.

The other formatting tips for the Button Bar include: making it required, initially selecting the first item, and changing the background selection color to white.

Note: This example uses the Mega Corp data.

Latest Date used to calculate 30-day window and Button Bar Footer

In this example, I use the Button Bar object as a footer to display the latest date but I will also assign the latest date to a parameter and then calculate a 30-day window for the objects in this report.

To do this, I will repeat the steps in the previous example to get the Button Bar to display the latest date. The new step in this example, is to store the button bar’s value into a parameter.

Let’s create a date parameter. From the Data pane, select New data item and click on Parameter…. For your parameter definition, select Date as the Type and then enter a minimum and maximum value appropriate for your data. You could select a current value but it is not required.

Then create your numeric representation of the date and change the aggregation.

Next, add the Button Bar object and assign the properties as shown. Notice that because we have the Parameter Role assigned there is no option to select the first item in the button bar.

Now we have our latest date displayed in the button bar and we have that value stored in the parameter. Next, we can use this parameter to create a 30-day window. To do this, we will need to create a calculated data item, I’m going to name it 30 Days, that represents the date for 30 days prior. I will use the latest date parameter and subtract 30 days. Then I can use this new data item, 30 Days, to create a filter so that the object only returns data for date values between the latest date and the previous 30 days.

From the Data pane, select New data item then select Calculated item….

Here is the expression for the 30 Days calculated data item. Notice that the inner expression converts the date parameter back to a number. Then I subtract 30 days. Then in the outer expression converts that number back into a date.

Now that we have our 30 Days prior date, we can create a Common Filter that only returns data for date values between the latest date and the previous 30 days. This Common Filter can then be used by any of the objects in the report!

Select any object in the report and from the Filters pane, select New filter then select Advanced filter.

Remember, we want to return the data where the date value is greater than or equal to our 30 Days data item.

Here is the filter expression. Be sure to give it a meaningful name and notice that the editor displays the number of returned rows for the evaluated expression.

Now we have a filter defined for one object. To convert this filter to a Common Filter so that all objects can use it, select the overflow menu on the filter and then select Change to common filter.

Now this common filter named Last30Days can be applied to any object. To apply this common filter, select a new object, then from the Filters pane, select New filter and then select the appropriately named common filter.

Take a look at the report example one more time and notice how I added titles to all of my objects so that the report viewer understands the data supporting each visualization.

Note: This example uses the Insight Toy data.

Latest Date used to calculate new measure and Dynamic Text Report Title

In this last example, I want to demonstrate how you can use the latest date to create a new calculated data item and use this value in an object. I also want to show how you can use the latest date in the Text object to serve as the report title and give you an alternative to the button bar we saw in the previous two examples.

I’ve used two different objects to visualize the same data: compare the latest date’s activity to the daily’s average across all the data available.

Here I use a Bar Chart with both measures assigned. I styled the average bar to be black for comparison.

For the second visualization, I use a Targeted Bar Chart where the average is assigned to the target role.

To create these visualizations, I needed to create two calculations for the Order Total measure:

Latest Date – Order Total
Daily Average – Order Total

I will be using the latest date parameter I defined from the previous example. A parameter is valid for use for the entire report across multiple pages, so I do not need to repeat those steps for this page of the report.

Latest Date – Order Total: This calculated data item will return the Order Total values for the latest date.

Daily Average – Order Total: This aggregated measure will return the daily average Order Total for the whole of the available data.

Once you have these two measures, assign the data roles for the objects is easy.

Lastly, let’s take a look at how I configured the Text object. If you are interested in learning more about dynamic capabilities of the Text object, please see my article: Using Dynamic Text in a VA 7.4 or VA 8.2 Report.

Note: This example uses the Insight Toy data.

Conclusion

Now you have seen several ways to use the latest date in your report: in a button bar, in a text box, in a filter to drive a moving window of time or in calculations to create new metrics for comparison.

All you need to remember is the Get the Latest Data Technique.

Create a new calculated item using the TreatAs function to get the numeric version of your date.
Change the aggregation of this numeric date to be non-additive, either minimum or maximum.
Add the date to your desired object.
Add a Rank to the object for a top count of 1 by the numeric version of your date.

If you’d like to learn more about using dates in your reports, check out these articles:

TimStettner · ‎11-23-2020

Awesome! One of the best and most helpful articles to find about date issues in VA!