SAS Visual Analytics: Create Interactive Post-Aggregate Filters
In this post, I demonstrate two ways to create interactive post-aggregate filters in SAS Visual Analytics:
- Create an aggregated data source and use this to populate a slider that filters a list table.
- Using expression-based parameters to dynamically set the range of a slider based on the aggregated values of a measure. Then, use the slider to act as an interactive post-aggregate filter for a list table.
The Scenario
Using the SASHELP.ORSALES table, we have one line per product group and quarter. I want an interactive slider filter to filter a list table of Product Category and Profit.
Select any image to see a larger version.
Mobile users: If you do not see this image, scroll to the bottom of the page and select the "Full" version of this post.
To this end, the slider should display the profits aggregated by Product Category.
Option 1: Using an aggregated data source
SAS Visual Analytics supports creating aggregated data sources on the fly that you can directly use as a data source for control and report objects. We can easily fulfil our requirement by creating an aggregated data source, based on which we can create the slider and the list table.
In SAS Visual Analytics, create a new report based on the SASHELP.ORSALES table. From the Data pane, click on the Data Source pane and select New data from aggregation of ORSALES:
Then, select Product Category and Profit in USD and select the Add button to add them to the aggregated data source. Click OK:
Since Product Category is the only category in the aggregated data source, Profit in USD will automatically be aggregated by Product Category (using the aggregation specified in the original data source).
Let’s add the slider. Be sure that the aggregated data source is selected from the Data pane. Then, from the Objects pane, drag a slider onto the canvas. With it selected, in the Roles pane, assign Profit in USD to it. Again, ensure that the aggregated data source is selected from the Data pane. Then, from the Objects pane, drag a list table onto the canvas. With it selected, in the Roles pane, assign Product Category and Profit in USD to it. Lastly, with the slider selected, in the Actions pan, under Object Links, check the box next to List Table - Product Category 1. Voilà, we are done! Try moving the slider (or double clicking it to enter an exact value) and watch how the list table is filtered:
Option 2: Using expression-based parameters
This method is slightly more involved, but more powerful. In this option, we will use the original, detail data for the slider and the list table. With this method, the list table that is being filtered does not have to be linked to the aggregated data source and can contain other detailed data items.
When you add a slider control to filter based on a measure, it is automatically configured such that its range corresponds to the range of the measure in the source table. This way, we don't have to manually update the slider's minimum and maximum value when the data changes. However, when I want the slider to display aggregated values, I need to create parameters for the minimum and maximum of the aggregated measure, and then assign these to the slider.
With the original detail data source selected, use the Objects pane to drag a slider onto the canvas and in the Roles pane, assign Profit in USD to it. Notice the range of Profit right now goes from $209.80 to $552,970.51. This is based on the range of profit in the source table, with one line per product category and quarter. In other words, there was a quarter where some product category only had a total profit of $209.80.
Underneath, I add a List Table displaying Product Category and Profit in USD:
Notice that the profits in the list table have a different range. This is based on the aggregated profit of each product category over all rows in the source table (in this case all quarters). This is the range we want to display on the slider. Instead of hardcoding it, we will create parameters that calculate the minimum product category profit and maximum product category profit.
In the Data pane, create a parameter:
Then, enter the name Minimum Product Category Profit, select the DOLLAR12.2 format and click on Edit as expression:
Enter the following expression:
AggregateTable(_Min_, Table(_Sum_, Fixed('Product Category'n), 'Profit in USD'n))
In the Data pane, right click on the parameter and select Duplicate. Right-click the duplicate and select Edit. Name the duplicate Maximum Product Category Profit, and change its expression to:
AggregateTable(_Max_, Table(_Sum_, Fixed('Product Category'n), 'Profit in USD'n))
Now, we can assign these parameters to the sliders' range minimum and maximum options:
Perfect, now I can configure the slider to filter the list table. First, from the Actions pane, under Parameter Links, I need to assign the selected range's start and end to parameters that I will call Slider range start and Slider range end, respectively:
Now, I can filter the list table. With the list table selected, from the Filter pane, I click on + New filter and select Profit in USD:
Since we don't want hardcoded values for the filter but use the parameter values, we need an advanced filter. But first, it is essential that we check the box next to Filter aggregated values, to indicate the filter should apply post-aggregation (after the profit has been aggregated by Product Category, in this case). Once this is done, click on the More button and then select Advanced edit to convert it to an advanced filter:
In the advanced filter, enter the following expression:
BetweenInclusive('Profit in USD'n, 'Slider range start'p, 'Slider range end'p)
We're done! Try moving the slider (or double clicking it to enter an exact value) and watch how the list table is filtered:
Conclusion
We saw two different ways to create interactive post-aggregate filters in SAS Visual Analytics. For more details about filters in general, see SAS Visual Analytics: How to add a filter to a report or Filtering in Visual Analytics - SAS Support Communities. If you wish to learn more about the AggregateTable expression, see SAS Visual Analytics: Understanding the AggregateTable Function for Beginners.
Find more articles from SAS Global Enablement and Learning here.
