New Max/Min Aggregate Measure for Date and Datetime Formats!

[#1]

Introduced in the SAS Visual Analytics Stable 2021.1.2 (June 2021) release is the support for additional Formats for an aggregated measure: Date and Datetime.  

What does this mean? It means that we can use either the Max or Min Aggregated operators on a date or datetime data item to use in the VA report. More importantly this means that the returned aggregate measure is just that, still an aggregate measure and we cannot, yet use it has a category data item.  

So, let’s take a look at how we can use this new feature as is and then I’ll show you a technique so you can use it a few additional ways too.    

Max/Min of Date Simple Examples

In this section, I will show you a few ways of using the Max or Min of a date in simple examples.  

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

Crosstab Object Example Description

Here in this report example, at the top, I have added the Max _ByGroup_ of the Actual Completion Date to the Crosstab object. You can see that like an aggregate measure, I included Frequency as a reference with its default of Sum aggregation, it returns the maximum value for that by group.  

I added the List Table object with a filter Action from the Crosstab object so that you can see the detailed data behind the Crosstab summarization. You can see that for the selected row: Obi-Wan Konobi – Recon Mission, there were 7 rows and the maximum completion date is 02/21/2017.    

Text Object Example Description

In this report, I added a Text object at the bottom to serve as a footer. As you can see, I used two different aggregated measures. The first is is the Min _ForAll_ of Start Date and the second is the Max _ForAll_ of Actual Completion Date. This will return the minimum value for Start Date in the data source and the maximum value for Actual Completion Date in the data source.     

Difference between _ByGroup_ and _ForAll_

There is a description in the SAS Documentation in the Working with Quick Calculations in a Report section.  

ByGroup means that the aggregation for the measure is determined by the categories assigned to the roles of the report object.  

ForAll means that the aggregation for the measure is determined for the entirety of the data source, regardless of the role assignments for the report object.  

Crosstab Object Example – Using a Quick Calculation

Per SAS Documentation, “a quick calculation is an aggregated measure that you can create automatically in the Data pane.” That means we can use this feature to create the Max or Min of a date or datetime data item.  

We will use the quick calculation to create a new aggregated measure for the data item Actual Completion Date. To do this, right-click on the Actual Completion Date data item and select New calculation.... Then in the Create Calculation window, select Max as the Type. By default, quick calculations use the _ByGroup_ context.  

  

Once you click OK, the new calculation is automatically added to the Aggregated Measure grouping. Use the chevrons to expand the data item properties for further inspection.  

Notice in the expression preview you can verify that the quick calculation uses the Max operator and the _ByGroup_ context.  

Now you can assign Actual Completion Date (Max) 1 as you would any other aggregated measure. In this example, I’ve assigned it to the Crosstab object. I have a List Table with filter Action defined so that you can clearly see that the maximum value for the ByGroup is being accurately returned.  

Text Object Example – Using a New Data Item

Now let’s walk through how to use the Expression Builder from the New Data Item menu to create the aggregated measures used in the Text object.  

I will first create an aggregated measure for the the maximum date for the Actual Completion Date for the entire data source. In the previous section I used the Quick Calculation which by default returns the _ByGroup_ context and in this case I will need the _ForAll_ context.  

From the Data pane, use the New data item menu and select Calculated item. Next we will use the Operators tab to set up the expression. This is the order in which I added elements:

1. Add Aggregated (simple) Max
2. Change the context from _ByGroup_ to _ForAll_
3. Add Numeric (advanced) TreatAs to the Max _ForAll_ operand
4. From the Data Items tab, add the Actual Completion Date data item
5. Change the Format to the desired date format

Remember to give your data item a meaningful name.  

You may be wondering why we need to use the TreatAs operator. This is because SAS dates are simply a numeric representation of days since January 1, 1960. See SAS Date, Time and Datetime Values in SAS Documentation for additional information. That means, we want to get the maximum value of a SAS date, and to do that we need to treat it as a number and not a formatted value.  

Okay, time to get the minimum Start Date for the entire data source, which means using the _ForAll_ context again. I could repeat these steps, or I could create a Quick Calculation and edit the context from _ByGroup_ to _ForAll_.  

Now that we have our aggregated measures, we can assign them to the Text object’s roles.  

 

Max/Min of Date Advanced Example

In this section, I will show you how to use the Max or Min of a date in an advanced example. This example is considered advanced since we will need to use both Aggregated Data Sources and Data Joins. I’ve linked out to additional articles for more details on both features.  

Our goal will be to calculate the maximum date of completed activity in our data source and apply a filter that only shows the last 30 days of activity.  

You may be wondering why this is considered an advanced example. And under normal circumstances, this isn’t a difficult task. In my article and accompanying YouTube about using Common Filters, I show you how to use the Now() operator to easily return the last 30 days of data. This is ideal if you have data that is being loaded regularly, but what if your data isn’t loaded regularly? What if your data is only loaded weekly? Or you have another interval you wish to calculate based on the last date of activity in the data?  

This is when you will need to use this method. The technique to calculate and use in a filter the last 30 days of activity is to:

1. Create an aggregated measure for the maximum date of activity in your data source.
2. Create an aggregated measure for 30 days prior to the maximum date of activity in your data source.
3. Create an aggregated data source including one category data item to join back to the original data source and the two aggregated measures for our dates.
4. In the aggregated data source, change the measures representing the dates’ aggregation properties to non-additive.
5. Create a data join result between the original data source and aggregated data source.
6. Now in the joined data result, be sure to also change the measures representing the dates’ aggregation properties to non-additive.
7. Add the report object, assign data roles from the data join result, and now the maximum date of activity and 30 days prior are measures that can be used in the filter definition.

Here are the screenshots that go along with each step:  

Step 1: Create an aggregated measure for the maximum date of activity in your data source. You can manually build the expression using the New data item as described above or you can use the Quick Calculation. Just be sure to use the _ForAll_ context in the Max operator.  

Step 2: Create an aggregated measure for 30 days prior to the maximum date of activity in your data source.

Since the representation for the maximum date is an aggregate measure representing the number of days since January 1, 1960, we can calculate the 30 days prior date by simply subtracting 30.  

Step 3: Create an aggregated data source including one category data item to join back to the original data source and the two aggregated measures for our dates.

You can pick any category to include in the aggregated data source since the values for the Max Date (All) and Last 30 Days Min will be the same for every row. I find it easier to select a category data item with low cardinality.  

Step 4: In the aggregated data source, change the measures representing the dates’ aggregation properties to non-additive.

When you expand the data item’s properties, change the aggregation type to either maximum or minimum.  

Step 5: Create a data join result between the original data source and aggregated data source.  

Step 6: Now in the joined data result, be sure to also change the measures representing the dates’ aggregation properties to non-additive.

It’s always an important step to verify your results. Notice how the values of Last 30 Days Min and Max Date (All) are impacted by the non-additive aggregation type.  

Step 7: Add the report object, assign data roles from the data join result, and now the maximum date of activity and 30 days prior are measures that can be used in the filter definition.  

Conclusion

The addition of both Date and Datetime formats to aggregated measures now allows for easier access and usage to Max and Min date values.  

For additional date examples refer to one of the below references:

• How to prompt for a date range in a SAS Visual Analytics report Blog Series:
  • Slider with Style Input of Range: Article | YouTube
  • Slider with Style Input of Single Value: Article | YouTube
  • Drop-down List: Article | YouTube
  • Text Input: Article | YouTube
• Use parameters to highlight data points in VA reports: Article | YouTube
• VA 7.4: Configure Report or Section Level Cascading Prompts
• VA 8.1: Cascading Prompts as Report and Page Prompts
• VA 8.1 Makes Report and Page Prompts Easy With Auto Controls
• SAS Visual Analytics 8.1: Configuring prompts with different source data: Blog | YouTube
• Pick your prompt placement! New control placement option in Visual Analytics 8.4
• VA Report Example: Moving 30 Day Rolling Sum
• Use the latest date in your VA report
• VA Report Example: Number of Days Profitable for the last 30 days
• Using Date Parameters in your SAS Visual Analytics Reports

More of my Visual Analytics related materials:

• VA Report Example: Use Text Input for custom search (sas.com)
• SAS VA Report Example: Add a scope to an aggregated measure: Article | YouTube
• SAS VA: Key Value Object now with Lattice Category!
• Build a date in SAS Visual Analytics reports: Article | YouTube
• Use the Report Review pane to ensure good VA Report performance Article | YouTube
• SAS Viya Jobs in a SAS Visual Analytics report
• Tips for working with multiple data sources in SAS Visual Analytics: Article | YouTube
• SAS Demo | The Power Behind a Hidden Data Role in SAS Visual Analytics
• SAS Visual Analytics Report Example: Percent of Total – For All, For Rows, and For Columns
• SAS Visual Analytics example: moving average

Find more articles from SAS Global Enablement and Learning here.