VA Report Example: Use a Display Rule to highlight the maximum of an average measure
A SAS Visual Analytics report requirement came in requesting to dynamically highlight the maximum of an average measure. The added level of complexity to this requirement is that it is to also support a hierarchy.
Whenever you see the requirement to dynamically highlight this translates into Display Rules in SAS Visual Analytics. Check out my Display Rule Series here:
Let’s look at the final solution. In this first click path through the hierarchy: Oceania – Australia, we can see how the first bar in the Bar Chart Object is being dynamically highlighted.
In this second click path through the hierarchy: South America – Argentina you can see again how the first bar in the Bar Chart Object is being dynamically highlighted.
Report Requirement
Desire to highlight the maximum value on a Bar Chart for the measure Average Gross Margin which is a calculation of Product Sale – Product Cost of Sale aggregated as an average. The category is a geographic hierarchy: Continent – Country – Region – City.
Solution Part 1: Average Gross Margin
Disclaimer: my data is not exactly representative of the request, so I had to work with the columns available to me.
Since this is a straightforward expression, we can use the power of SAS Visual Analytics’ default aggregation to do a lot of the work. However; you can use a more complex expression, so I will show both the simple and complex, in case you need to substitute an advanced expression to meet your needs.
Simple Expression: Create a Calculated Data Item and use the default Average Aggregation Notice in this simple expression, I create a Calculated Data Item. I then assign the Average Aggregation behavior from the Data properties pane.
Complex Expression: Create an Aggregated Measure In the complex expression, I create an Aggregated Measure. In this expression, I define the average aggregation but using a ratio operator. I wanted to show you can use this solution for more complex expression if needed. You do not have to create the aggregated measure and you can instead use the default aggregation if your business needs suit.
If you need additional information about a Calculated Data Item versus an Aggregated Measure, you can reference my Article or YouTube.
Here I wanted to show that we get the same results across the different levels of the hierarchy:
Solution Part 2: Find the Maximum Average Gross Margin
In order to find the maximum of a group of cells, I use the AggregateCells Aggregated (tabular) Operator. See the SAS Visual Analytics Documentation: Reference: Operators and Functions for Data Expressions for more detail.
Notice in the below screenshots, I will set the aggregation type to _Max_ and I will set the start-index and the end-index to encompass all of the cells in the visualization object.
I include both screenshots using the simple and complex expressions as defined above.
AggregateCells Expression: Using the Simple Expression Notice here, I simply plug in the Calculated Data Item that I created earlier.
AggregateCells Expression: Using the Complex Expression Notice in this example, I do not plug in the previously created Aggregated Measure. You can, but I wanted to illustrate that you can put an aggregated measure expression inside the AggregateCells operator.
Again, here is a screenshot looking at our results when added to the Crosstab object. We get the same, as expected.
Solution Part 3: Assign Data Roles and Define Display Rule
Before I add the Bar Chart Object to the report, I will define the Display Rule for the Crosstab objects. This solution works for any visualization object, which is the true power behind SAS Visual Analytics. Some of the Objects will allow for Hidden or Data Tip Roles which allow you to hide the Max Gross Margin data item but other Objects will require both data items to be assigned a Role in order to define a Display Rule.
Here is the Display Rule definition: If the Average Gross Margin = Max Gross Margin then highlight graph green.
Since I have two sets of expressions for simple and complex, I duplicated the Display Rule. Notice that for the bottom Crosstab object there are two rows highlighted. That is because for each level in the hierarchy it will highlight the level’s maximum!
Now we can add the Bar Chart Object to the report, assign Data Roles and define the Display Rule. The Bar Chart is one of the Objects that allows you to use any measure combination in the Display Rule definition without requiring it to be assigned an explicit Role.
And even snazzier, since the requirement came in saying the hierarchy was a geographic hierarchy, you can even see this on a Geo Map Object. In order to be able to define the Display Rule for this Object, you can add the Max Gross Margin to the Data tip value Role.
Conclusion
I have now shown you how you can use the AggregateCells Operator to get the maximum of an average measure. You can then use Display Rules to conditionally highlight the maximum value using a variety of visualization objects.
If you need additional information on Display Rules or would like step-by-step instructions, please refer to my series here:
If you need additional information about a Calculated Data Item versus an Aggregated Measure, you can reference my Article or YouTube.
And here is a list of additional Visual Analytics report examples:
- VA Report Example: Use Display Rules to get diagonal coloring on a Crosstab
- VA Report Example: Applying a Color-Mapped Display Rule for a Dual Axis Bar-Line Graph
- VA Report Example: Moving Average
- VA Report Example: Percent of Total – For All, For Rows, and For Columns
- VA Report Example: Moving 30 Day Rolling Sum
- VA Report Example: Number of Days Profitable for the last 30 days
- VA Report Example: Current Month vs Previous Month
- VA Report Example: Month Average versus Total Average
- VA Report Example: Ways to use the Cumulative Period operator
- VA Report Example: Relative Period Report in SAS Visual Analytics
- VA Report Example: Distinct Count If – Run multiple queries at once
- VA Report Example: Add a scope to an aggregated measure: Article | YouTube
Hi, with the current version, I am struggling to get the formula working with error message ''Expected: Numeric constant, Numeric, Date or DateTime Found: Aggregated' . Appreciate if please help out to fix the formula. Many thanks.
Hi,
Based on the name of your calculation, since you have the word MAX in there - I thought you were trying to get the final formula. Here's what it would look like using the new UI.
Let me know if you're still having trouble.
Thank you,
Teri
