SAS VA Report Example: Add a scope to an aggregated measure

4 Likes

A scoped aggregated measure calculation is an expression defined for a specific crossing of categories. When thinking of a crossing of categories, it’s easiest to visualize a Crosstab object and the intersecting columns. The purpose of adding a scope to an aggregated measure, is that you want a specific type of expression returned for that intersection which is different from the other base cells around it.

I have recorded an accompanying video tutorial for these examples so that you may follow along.

Identify the Intersection

In this example below, we have Facility Region assigned to the Rows Role and Date by Year assigned to the Columns Role. I’ve highlighted the value $1,466,314 and we can see the intersection of South and 2010 clearly.

Abstractly, we would identify all of these cells as the intersection: Facility Region/Date by Year. So in this case, we do not yet have any unique intersections to manipulate with a scoped aggregated measure.

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

Adding an additional Row Role of Facility and turning on Subtotals will give us additional cells that we can use to apply a scoped aggregated measure. Now take a look at the Crosstab object. Notice the same value $1,466,314 is circled below, but since we have the additional Row assignment we have more cells, and we can now define a scoped expression for the intersection: Facility Region/Date by Year which is highlighted in purple below.

Add a scope to an aggregated measure

First, it’s good to understand the current base expression for all of these cells. The base expression, in this example, is simple and SUMs all of the underlying values _ ByGroup _ .

For a straightforward clear example, let’s scope the expression for the intersection Facility Region/Date by Year to be the AVG for each _ ByGroup _ .

Start by creating a new Calculated item by using the + New data item menu.

Next, let’s create the base expression for all of the cells to use. Remember, that’s the SUM _ ByGroup _ . Notice that once you add an Aggregated Operator to the expression that it is assigned to the Base expression scope automatically.

Next, we need to add the scope for the Facility Region/Date by Year intersection to be the AVG for each _ ByGroup _ .

First, use the + button to add a scope. Next, define the intersection by moving over the columns, hint: order matters. And at the bottom, you can leave the Start scope with Blank expression selected then click OK.

Once the scope’s intersection has been defined then we can build the expression. Remember, in this example we want to expression to be the AVG for each _ ByGroup _ .

If we add our new expression to the Crosstab object, we can see how the base expression is used for all of the cells except for the intersection Facility Region/Date by Year where we can see the scoped expression being used.

Scoped Aggregated Measures in other VA Objects

From this simple example, you can easily see how the scoped aggregated measure is used in this Crosstab object. But scoped aggregated measures can be used in other VA Objects too. You just have to be sure you are meeting the intersection criteria if you want the object to use the scoped expression versus the base expression.

In the below example, I have shown two Bar Chart Objects with the same scoped aggregated measure assigned to the Measure Role: Expenses (staffing) Scope with Avg. The only difference in Role assignments is that to meet the scoped intersection criteria, I have assigned the Date by Year to the Group role.

One nice thing about the auto generated titles for the Bar Chart object, is that you can easily see the difference in the blue Bar Chart where there is only Facility Region versus the red Bar Chart where there is Facility Region grouped by Date by Year.

Tip to identify complex Intersections

I have gone through a simple example, but what if you had a more complex Crosstab object that you needed to create scoped aggregated measures for? How can you determine the intersections? The easiest way is to use Display Rules to assist.

Create a Display Rule for any measure where the condition will be met, and then color the background of the cells different colors so that you can easily determine the intersections.

Notice that I kept the same purple highlighting for the intersection Facility Region/Date by Year consistent with the above example.

To do this, you will need to define a new Display Rule for each available intersection.

Limitations of Scoped Aggregated Measures

One of the limitations you might run into, is if you want a scoped expression to have a different format from the base expression. Unfortunately, at this time, there is no way to dynamically assign formats.

Therefore, as in the below example, choose to use a COMMA or NUMERIC format for the measure so that the report viewers do not make the wrong assumption about the measure.

The base expression in this example is a Quick Calculation generated from the Expenses (staffing) column.

The scoped aggregated measure definition looks like this. Notice that I used the COMMA format.

Additional VA Report Examples and aggregated measure tips:

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

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

Find more articles from SAS Global Enablement and Learning here.