Chapter 11: Advanced Topics – Creating Advanced Data Items

11.1 Introduction

As discussed in Chapter 3, SAS Visual Analytics enables you to calculate new data items from your existing data items by using an expression. For example, you might want to calculate a company’s profits by subtracting expenses from revenues. In addition to performing mathematical calculations on numeric values, you can use calculated data items to create date and time values. For example, if your data contains separate categories for month, day, and year, then you can calculate a date value from those categories. To recap:

● All calculations are performed on unaggregated data. The calculation expression is evaluated for each row in the data source before aggregations are performed. To perform calculations on aggregated data, see Section 11.3.

● Calculated data items can accept parameters. For more information, see Section 11.2.

● A hierarchy can contain calculated data items as long as they are categories.

● Calculated data items can be changed into geographic data items and used in geo maps.

Figure 11.1: Creating Data Items

The following types of data items can be created in SAS Visual Analytics using code or in SAS Data Studio or SAS Enterprise Guide.

Table 11.1: Data Items

Item	Description
Custom category	A custom category creates labels for groups of values of category or measure data items. When you create a custom category from a measure data item, you can use intervals or distinct values to group the data. For more information about custom categories, see “Working with Custom Categories in a Report” in the SAS Visual Analytics: Working with Report Data documentation.
Duplicate	Both measures and categories can be duplicated (copied) in Visual Analytics. Duplicating measures enables you to compare the data using different aggregations in a table or graph or change the classification to a category for grouping other values in tables or graphs. Duplicating datetime values enables you to apply different formats to the values for use in tables or graphs. Duplicating calculated items enables you to make variations to a calculation. For more information about duplicating data items, see “Working with Data Items in a Report” in the SAS Visual Analytics: Working with Report Data documentation.
Calculated item	Calculated items are created by performing mathematical calculations on numeric values or by performing operations on datetime data items or categories. All calculations are performed on unaggregated data. That is, the expression is evaluated for each row in the data source. For more information about creating calculated data items, see “Working with Calculated Items in a Report” in the SAS® Visual Analytics: Working with Report Data documentation. For more information about operators, see “Reference: Operators for Data Expressions” in the SAS® Visual Analytics: Working with Report Data documentation.

The following types of data items need to be created in Visual Analytics.

Table 11.2: SAS Visual Analytics Data Items

Item	Description
Geography	A geography data item is a category whose values are mapped to geographical locations or regions. Geography data items can be used with geo maps and other report objects. Geography data items can be created using predefined roles (for example, country names), by associating latitude and longitude coordinates with the values (custom), or by associating polygon data from a separate data source with map regions (custom). For more information about creating geography data items, see “Working with Geography Data Items” in the SAS Visual Analytics: Working with Report Data documentation.
Aggregated measure	Aggregated measures enable you to calculate new data items using aggregated values. This means that the calculation changes depending on the other data items available in the graph. For example, you can see the profit margin for each region or by each store. For more information about creating calculated data items, see “Working with Calculated Items in a Report” in the SAS Visual Analytics: Working with Report Data documentation.
Hierarchy	A hierarchy is a defined arrangement of category data items based on a parent-child relationship. In many cases, the levels of the hierarchy are arranged with the more general information at the top (for example, year) and the more specific information at the bottom (for example, month). Hierarchies enable you to add drill-down functionality to graphs. Hierarchies that consist of all geographic data items are considered geographic hierarchies and can be used in geo maps. Note: You can create a date hierarchy from a date data item. The date hierarchy, by default, has levels for year, quarter, month, and day. A date hierarchy created from a datetime data item has levels, by default, for year, quarter, month, day, hour, minute, and second. For more information about hierarchies, see “Working with Hierarchies in a Report” in the SAS Visual Analytics: Working with Report Data documentation.
Distinct count	A distinct count counts the number of distinct values of a category data item as an aggregated measure. This means that the calculation changes depending on the other data items available in the graph. For example, you can see the number of orders placed for each age group or the number of orders placed for each country by creating a distinct count from the order ID. For more information about creating distinct counts, see “Working with Data Items in a Report” in the SAS Visual Analytics: Working with Report Data documentation. Note: If the category contains missing values, the distinct count is increased by one. A configuration setting can modify this behavior.
Parameter	A parameter is a variable whose value can be changed and that can be referenced by other report objects. Parameters can be used in control objects in Visual Analytics. When the value of the control changes, the parameter is updated with that value, and any report objects that reference that parameter are updated as well. Parameters can be used in calculations, display rules, filters, and ranks, URLs, and text objects. For more information about parameters, see “Working with Parameters in Reports” in the SAS Visual Analytics: Working with Report Data documentation.
	Derived data items are aggregated measures that display values for the measure and the formula type on which the derived item is based.
	The following types of derived items can be created from category data items:
	Distinct count	Displays the number of distinct values for the selected category. For more information, see the distinct count row above.
	Count	Displays the number of nonmissing values for the selected category.
	Number missing	Displays the number of missing values for the selected category.
	The following types of derived data items can be created from measure data items:
	Cumulative total	Displays a running total of all the values for the measure on which it is based.
Derived item	Data suppression	Obscures aggregated data if individual data values could easily be inferred. Data suppression replaces all values for the measure on which it is based with an asterisk (*) unless a value represents the aggregation of a specified minimum number of values. For more information, see “Reference: Operators for Data Expressions” in the SAS Visual Analytics: Working with Report Data documentation.
	Difference from previous period	Displays the difference between the value for the current time period and the value for the previous time period.
	Difference from previous parallel period	Displays the difference between the value for the current time period and the value for the previous parallel time period within a longer time interval.
	Moving average	Displays a moving average (rolling average) for the measure on which it is based. The moving average calculates the average for each value with the specified number of preceding values.
	Percent difference from previous period	Displays the percentage difference between the value for the current time period and the value for the previous time period.
	Percent difference from previous parallel period	Displays the percentage difference between the value for the current time period and the value for the previous parallel time period within a longer time interval.
	Percent of subtotals	Displays the percentage of the subtotal value for the measure on which it is based. You can create a percentage of subtotal only when the source data item has an aggregation of Sum or Count. Note: The Percent of subtotals derived item is available for use only in crosstabs. Note: The Percent of subtotals derived item is relative to the subset of data that is selected by your filters and ranks.
	Percent of total – sum	Displays the percentage of the total value for the measure on which it is based. You can create a percentage of total only when the source data item has an aggregation of Sum or Count. Note: The Percent of total – sum derived item is relative to the subset of data that is selected by your filters and ranks.
	Period to date	Displays the aggregated value for the current time period and all of the previous time periods within a larger time interval.
	Year to date	Displays the aggregated value for the current time period and all of the previous time periods within the year. The year-to-date calculation subsets the data for each year using today’s date (where today is evaluated each time you view the report).
	Year to date growth	Displays the percentage difference between the year-to-date value for the current time period and the year-to-date value for the same time period of the previous year. The year-to-date calculation subsets the data for each year using today’s date (where today is evaluated each time you view the report).
	Year over year growth	Displays the percentage difference between the current time period and an equivalent time period from the previous year. The year-over-year calculation subsets the data for each year using today’s date (where today is evaluated each time you view the report).
	For more information about derived items, see “Working with Data Items in a Report” in the SAS Visual Analytics: Working with Report Data documentation.

11.2 Creating Calculated Items

To recap, calculated data items are created by performing mathematical calculations on numeric values or by performing operations on datetime data items or categories. All calculations are performed on unaggregated data. That is, the expression is evaluated for each row in the data source. For example, you can create a new calculated item (New Salary) by multiplying salary and increase. For each row in the data source, the operation is evaluated, and the new data item is created. Then, when that data item is used in a graph (for example, with Gender) the values for New Salary are totaled for each gender.

Figure 11.2: Example of a Calculated Item

Vector Plot

A vector plot shows the change in the value of two measures using directed line segments (vectors) to represent both the direction and magnitude of the change at each point. Each set of measures shows information about the starting point and the ending point.

Demo 11.1: Creating a Numeric Calculated Item

This demonstration illustrates how to create a numeric calculated item and a vector plot in a report.

1. From the browser window, sign in to SAS Viya.

2. In the upper left corner, click (Show list of applications) and select Explore and Visualize.

SAS Visual Analytics appears.

3. Click All Reports.

a. Navigate to the Courses/YVA285/Advanced/Demos folder.

b. Double-click the VA2-Demo7.1a report to open it.

4. View roles available for the vector plot and the data items available in the table.

a. In the canvas, click the vector plot to select it.

b. In the right pane, click Roles.

The vector plot requires two groups of measures: one set for the X axis and one set for the Y axis. For each set, a measure is required for the start time (origin) and a measure is required for the end time.

c. In the left pane, click Data.

Transaction Date is in the format MMMYYYY. We have customer order information for one year. For the vector plot, we would like to compare Product Quality and Customer Satisfaction for two points during the year: January and December.

We have already calculated Customer Satisfaction at both time points (January and December) and Product Quality for December. We still need to calculate Product Quality for January.

5. Calculate Product Quality for January.

a. On the Data pane, select New data item  Calculated item.

b. In the Name field, enter Product Quality (January).

c. For the Result Type field, verify that Automatic (Numeric) is selected.

d. For the Format field, click (Edit).

i. In the Format window, select Percent.

ii. For the Width field, verify that 12 is specified.

iii. For the Decimals field, verify that 2 is specified.

iv. Click OK.

e. On the left side of the window, click Operators.

f. Expand the Boolean group.

g. Double-click the IF…ELSE operator to add it to the expression.

h. Expand the Comparison group.

i. Drag x=y to the condition field in the expression.

j. Expand the Date and Time group.

k. Drag Month to the number field on the left of the equal sign.

l. Right-click the No selection field in the expression and select Replace with  Transaction Date.

m. Enter 1 in the number field on the right of the equal sign.

n. Right-click the number field for the RETURN operator and select Replace with  Product Quality.

o. Right-click the number field for the ELSE operator and select Replace with  Missing Value.

The expression should resemble the following:

p. In the lower right corner of the window, click OK.

The new data item is added to the Data pane.

q. Next to Product Quality (January), click (Edit properties).

r. For the Aggregation field, select Average.

6. Assign data items to the vector plot.

a. In the canvas, click the vector plot to select it.

b. In the right pane, click Roles, if necessary.

c. For the X axis role, select Add  Product Quality (December).

d. For the Y axis role, select Add  Customer Satisfaction (December).

e. For the X Origin role, select Add  Product Quality (January).

f. For the Y Origin role, select Add  Customer Satisfaction (January).

g. For the Group role, select Add  Product Line.

h. For the Data tip values role, click Add.

i. Select Product Brand and click OK.

The vector plot should resemble the following:

The grouping of arrows in the upper left corner are product lines in the Toy brand. For these product lines, there is a negative change in both quality and satisfaction over the year.

The grouping of arrows in the lower right corner are product lines in the Novelty brand. For these product lines, most have also declined (in both quality and satisfaction) over the year. On the other hand, the Kiosk product line has a marginal improvement in Product Quality but still a reduction in Customer Satisfaction. We might need to further analyze the products to determine why Customer Satisfaction is trending down.

7. Save the report.

Business Scenario

As a challenge, you calculate employee tenure. For retired employees, we want to know how many years they worked for the company as of their retirement date. For current employees, we want to know how many years they have worked for the company as of today’s date.

Butterfly Chart

A butterfly chart (also known as a tornado chart) displays two bars with a shared category axis, where the baselines of the two bar charts are located in the center of the chart.

Figure 11.3: Butterfly Chart

Practice 11.1

1. Creating a Numeric Calculated Item

a. Open the browser and sign in to SAS Viya.

b. Open the VA2-Practice7.1a report in the Courses/YVA285/Advanced/Practices folder.

c. Create a new data item, Salary (Europe), that averages salaries for European employees.

Hint: Employee Country has the two-letter abbreviations for each country and can be used to calculate the new data item. For this data, all countries except Australia (AU) and United States (US) are in Europe.

d. Assign the following data items to the specified roles for the butterfly chart:

Category	Job Title
Measure (bar)	Salary (Europe)
Measure (bar 2)	Salary (America/Oceania)

e. Answer the following questions:

Are there any jobs that do not exist in Europe?

Answer:

Are there any jobs in which Europeans make more than other continents (on average)?

Answer:

The butterfly chart should resemble the following:

f. Save the report.

Challenge (Optional)

1. Creating an Advanced Numeric Calculated Column

a. Open the browser and sign in to SAS Viya.

b. Open the VA2-Practice7.1a report in the Courses/YVA285/Advanced/Practices folder.

c. Create a new data item, Employee Tenure, that calculates how many years each employee has been with the company.

Hint: For retired employees, we want the number of years at the time of retirement. For active employees, we want the number of years as of today’s date.

Note: In order to match the displayed results, use the FLOOR operator when calculating Employee Tenure.

d. Answer the following question:

What is the average employee tenure for retired Sales Rep. III employees? For retired Temp. Sales Rep. employees?

Answer:

The bar chart should resemble the following:

e. Save the report.

Needle Plot

A needle plot displays vertical line segments (needles) connected to a horizontal baseline. The horizontal baseline can be at zero (shown above for profit) or can be at some other value (above which signals improvement and below which signals decline). Measures can be assigned to both the vertical and horizontal axis. Needle plots are often used in clinical trials to indicate the effect of drugs on various patients.

Figure 11.4: Needle Plot

Demo 11. 2: Creating a Character Calculated Item

This demonstration illustrates how to create a character calculated item and a needle plot in a report.

1. From the browser window, sign in to SAS Viya.

2. In the upper left corner, click (Show list of applications) and select Explore and Visualize.

SAS Visual Analytics appears.

3. Click All Reports.

a. Navigate to the Courses/YVA285/Advanced/Demos folder.

mm. Double-click the VA2-Demo7.1b report to open it.

4. In the canvas, view the list table.

City contains the area code of the location in parentheses. Some cities share an area code (for example, Asheville and Black Mountain in the list table above). For the needle plot, we would like to view total profit for each area code.

5. Calculate area codes.

a. In the left pane, click Data.

b. Select New data item  Calculated item.

c. In the Name field, enter Area Codes.

d. For the Result Type field, select Character.

e. On the left side of the window, click Operators.

f. Expand the Text (advanced) group.

g. Double-click the Substring operator to add it to the expression.

h. Right-click the string field in the expression and select Replace with  City.

i. On the left side of the window, expand the Text (advanced) group, if necessary.

j. Drag the FindChar operator to the first number field in the expression.

k. Right-click the first string field for the FindChar operator and select Replace with  City.

l. Enter ( in the second string field for the FindChar operator.

m. Enter 9 in the last number field in the expression.

The expression should resemble the following:

n. In the bottom right corner of the window, click Preview.

We need to remove the parentheses from the new data item, Area Codes. The RemoveChars operator can do this.

o. Click Close to close the Preview Result window.

p. On the left side of the window, expand the Text (advanced) group, if necessary.

q. Drag the RemoveChars operator to the outside of the expression.

r. Enter () in the string field for the RemoveChars operator.

The expression should resemble the following:

s. In the bottom right corner of the window, click Preview.

t. Click Close to close the Preview Result window.

u. In the lower right corner of the window, click OK.

The new data item is added to the Data pane.

6. Assign data items to the needle plot.

a. In the canvas, click the needle plot to select it.

b. In the right pane, click Roles, if necessary.

c. For the X axis role, select Add  Area Codes.

d. For the Y axis role, select Frequency  Profit.

The needle plot should resemble the following:

A majority of our profit comes from the area codes 336 and 919/984. Three area codes generate profit below the $50,000 minimum (252, 828, and 910).

7. Save the report.

Dot Plot

A dot plot displays data for each value of a category data item using dots. The position of each dot on the response axis represents the summarized value of the measure. Dot plots are similar to bar charts but minimize the amount of chart junk (visual elements in graphs that are not necessary to understand the graph or distract from the main message of the graph). Dot plots are often preferred because bar charts can distort the values if the area of the bars is compared and not the height. Dot plots can also be a good alternative to a bar chart to highlight differences between values that are close together. By starting the vertical axis at a nonzero value (as shown in Figure 11.5), you can more easily compare the differences than you could with a bar chart.

Figure 11.5: Graphs (Dot Plot)

Practice 11.2

1. Creating a Character Calculated Item

a. Open the browser and sign in to SAS Viya.

b. Open the VA2-Practice7.1b report in the Courses/YVA285/Advanced/Practices folder.

c. Create a new data item, Product Code, that takes the following form:

<first eight characters of Facility> – <first three characters of Unit>

d. Assign the following data items to the specified roles for the dot plot:

Category	Product Code
Measure	Frequency
Data tip values	Facility City

e. Add a rank to the dot plot to show the top five products by frequency.

f. Answer the following question:

Where are the top five products produced?

Answer:

The dot plot should resemble the following:

g. Save the report.

Challenge (Optional)

1. Creating a Date Calculated Item

a. Open the browser and sign in to SAS Viya.

b. Open the VA2-Practice7.1b (Challenge) report in the Courses/YVA285/Advanced/Practices folder.

c. Create a new data item, Facility Closing Date, that is 20 years after the facility opened (on December 31).

d. Assign the following data items to the specified roles for the dot plot:

Category	Facility Closing Date
Measure	Number of Facilities

e. Answer the following question:

How many facilities will be closed in 2020?

Answer:

The dot plot should resemble the following:

f. Save the report.

11.3 Creating Aggregated Measures

Conversely, aggregated measures enable you to use aggregated values to calculate new data items. These data items are created by first aggregating values, and then by performing the operation. With aggregated measures, the calculation changes depending on other data items that are available in the graph. For example, you can create a new aggregated measure (Salary (Percent of total)) by dividing the sum of the salary for each group by the sum of the salary for all groups. In this instance, because Salary (Percent of total) is paired with Gender, salaries are first totaled for each gender. Next, salaries are totaled for all genders. Then, the operation is evaluated for each gender to show the contribution to total salary. You could take the same aggregated measure and pair it with Country to see the contribution of each country to total salary. When you create aggregated measures, you need to specify specific parameters for each aggregated operator. For simple aggregated operators, the aggregation context is required.

Figure 11.6: Example of an Aggregated Measure

Note: Distinct counts and derived data items are special types of aggregated measures.

The aggregation context is the extent to which the aggregation is calculated. The _ByGroup_ context will aggregate the values for each group (the ones created in the report object), while the _ForAll_ context will aggregate the values for the entire set of data. The dynamic nature of aggregated measures makes them extremely useful for analysis and reporting.

For each aggregation in your expression, select the aggregation context. A drop-down list beside each aggregation enables you to select one of the following context values:

ByGroup

calculates the aggregation for each subset of the data item that is used in a visualization. For example, in a bar chart, an aggregated measure with the ByGroup context calculates a separate aggregated value for each bar in the chart.

ForAll

calculates the aggregation for the entire data item (after filtering). For example, in a bar chart, an aggregated measure with the ForAll context uses the same aggregated value (calculated for the entire data item) for each bar in the chart.

By using the ForAll and ByGroup contexts together, you can create measures that compare the local value to the global value. For example, you might calculate the difference from mean by using an expression such as the following: Avg ByGroup(X) - Avg ForAll(X)

Figure 11.7: Aggregation Context

Demo 11.3: Creating an Aggregated Measure

This demonstration illustrates how to create an aggregated measure and a region geo map in a report.

1. From the browser window, sign in to SAS Viya.

2. In the upper left corner, click (Show list of applications) and select Explore and Visualize.

SAS Visual Analytics appears.

3. Click All Reports.

a. Navigate to the Courses/YVA285/Advanced/Demos folder.

b. Double-click the VA2-Demo7.2a report to open it.

4. View details table for the geo map.

a. In the upper right corner of the geo map, click (Maximize).

b. In the details table at the bottom of the window, click Product Sale twice to sort in descending order.

The country with the highest sales (US) also has the highest number of customers. Region geo maps should be used with measures that are evenly distributed within each region. We need to create a new aggregated measure that takes the total sales for the region and divides by the number of customers.

c. In the upper right corner of the geo map, click (Restore).

5. Calculate a new aggregated measure, Sales by Customer.

a. In the left pane, click Data.

b. Select New data item  Calculated item.

c. In the Name field, enter Sales by Customer.

d. For the Result Type field, select Aggregated Measure.

e. On the left side of the window, click Operators.

f. Expand Numeric (simple).

g. Double-click the x/y operator to add it to the expression.

h. Expand Aggregated (simple).

i. Drag Sum to the number field on the left of the division sign.

j. Verify that _ByGroup_ is specified for the Sum operator.

k. Right-click the number field for the Sum operator and select Replace with  Product Sale.

l. On the left side of the window, drag Distinct to the number field on the right of the division sign.

m. Verify that _ByGroup_ is specified for the Distinct operator.

n. Right-click the number field for the Distinct operator and select Replace with  Customer.

The expression should resemble the following:

6. In the upper right corner of the window, for the Format field, click (Edit).

a. In the Format window, expand Currency (basic).

b. Select Dollar.

c. For the Width field, verify that 12 is specified.

d. For the Decimals field, verify that 2 is specified.

e. Click OK.

i. Click OK to create the new aggregated measure.

The Data pane should resemble the following:

7. Modify the geo map to use the new aggregated measure.

a. In the canvas, click the geo map to select it.

b. In the right pane, click Roles.

c. For the Color role, select Product Sale  Sales by Customer.

The geo map should resemble the following:

d. In the upper right corner of the geo map, click (Maximize).

e. In the details table at the bottom of the window, click Sales by Customer twice to sort in descending order.

If we look at sales by customer, Sweden ranks first and United States ranks second. Now there is an even distribution of the values in the coordinate geo map, and we can more accurately compare countries.

g. In the upper right corner of the geo map, click (Restore).

8. Save the report.

Practice 11.3

1. Creating an Aggregated Measure

a. Open the browser and sign in to SAS Viya.

b. Open the VA2-Practice7.2a report in the Courses/YVA285/Advanced/Practices folder.

c. Create a new aggregated measure (Yield Rate) that has the following expression:

Total production for each group / Total capacity for each group

d. Add Yield Rate to the crosstab.

The crosstab should resemble the following:

e. Save the report.

Periodic Operator

Periodic operators in SAS Visual Analytics enable you to build relative period calculations for month, quarter, and year. For example, you might want to see a metric for every month, the previous month’s value next to it, and lastly the difference between the two. The periodic operators have a new additional parameter that controls how filtering on the date data item used in the calculation affects the aggregated measure calculations such as the difference between parallel periods, year to date cumulative calculations, and so on.

Figure 11.8: Relative Period Operator

The RelativePeriod operator returns aggregated values for a period of time that is relative to the current period.

Note: The following values for the time filters parameter are available:

Parameter	Description
_ApplyAllFilters_	Applies all filters (object, prompt, actions) before the measure is calculated.
_IgnoreAllTimeFrameFilters_	Applies all filters (object, prompt, actions) based on the same date data item after the measure is calculated.
_IgnoreInteractiveTimeFrameFilters_	Applies object filters based on the same date data item before the measure is calculated, but applies interactive filters (prompt, actions) after the measure is calculated.

Note: For the date parameter, only date data items whose formats specify a year are available.

Note: For the aggregation interval parameter, _Inferred_ automatically selects an interval using the format for date data items used with the aggregated item.

Note: The scope parameter specifies how much of each period is aggregated. The following values are available:

Parameter	Description
_Full_	Aggregates values for the entire period.
_ToDate_	Aggregates values up to a specific day within a period.
_ToToday_	Aggregates values up to the equivalent of today’s position in the current interval. The value for today is evaluated dynamically whenever the aggregated measure is viewed in a report.

For more information about Aggregated (Periodic) Operators, see “Reference: Operators for Data Expressions” in the SAS Visual Analytics: Working with Report Data documentation.

Demo 11.4: Creating a Periodic Aggregated Measure

This demonstration illustrates how to create and modify a periodic aggregated measure.

1. From the browser window, sign in to SAS Viya.

2. In the upper left corner, click (Show list of applications) and select Explore and Visualize.

SAS Visual Analytics appears.

3. Click All Reports.

a. Navigate to the Courses/YVA285/Advanced/Demos folder.

b. Double-click the VA2-Demo7.2b report to open it.

4. Create a new periodic aggregated measure, Order Total (3M change).

a. In the left pane, click Data.

b. Select New data item  Calculated item.

c. In the Name field, enter Order Total (3M change).

d. For the Result Type field, select Aggregated Measure.

e. On the left side of the window, click Operators.

f. Expand Numeric (simple).

g. Double-click the x-y operator to add it to the expression.

h. Expand Aggregated (periodic).

i. Drag RelativePeriod to the number field on the left of the minus sign.

j. For the Aggregation Type operator, verify that _Sum_ is specified.

k. Right-click the number field for the Measure operator and select Replace with  OrderTotal.

l. For the Time Filters operator, select _ApplyAllFilters_.

m. For the Date operator, select Transaction Date.

n. For the Aggregation Interval operator, select _ByMonth_.

o. For the Offset Interval operator, verify that 0 is specified.

p. For the Scope operator, verify that _Full_ is specified.

q. Right-click the RelativePeriod operator and select Copy.

r. Right-click in the number field on the right of the minus sign and select Paste.

s. For the Offset Interval operator, on the right of the minus sign, enter -3.

The expression should resemble the following:

t. In the upper right corner of the window, for the Format field, click (Edit).

i. In the Format window, expand Currency (basic).

ii. Select Dollar.

iii. For the Width field, verify that 12 is specified.

iv. For the Decimals field, verify that 2 is specified.

v. Click OK.

u. Click OK to create the new periodic aggregated measure.

The Data pane should resemble the following:

5. Modify the crosstab to use the new periodic aggregated measure.

a. In the canvas, click the crosstab to select it.

b. In the right pane, click Roles.

c. For the Measures role, click Add.

d. Select Order Total (3M change) and click OK.

The crosstab should resemble the following:

6. Filter the crosstab to view details since January 2016 and modify the periodic aggregated measure.

a. For the slider control, click on the left side of the slider control.

i. Click (Select a month and year).

ii. For the Month field, select January.

iii. For the Year field, enter 2016.

iv. Click OK.

The crosstab should resemble the following:

Even though the table contains details for Order Total in previous months, we specified _ApplyAllFilters_ for the calculation, which applies the filters first before calculating the aggregated measure.

b. In the left pane, click Data.

c. Right-click Order Total (3M change) and select Edit.

i. For the Time Filters operator on the left of the minus sign, select _IgnoreInteractiveTimeFrameFilters_.

7. For the Time Filters operator on the right of the minus sign, select _IgnoreInteractiveTimeFrameFilters_.

The expression should resemble the following:

8. Click OK to apply the changes.

The crosstab should resemble the following:

The _IgnoreInteractiveTimeFrameFilters_ option ignores any interactive filters from prompts and actions that are based on the same date data item used in the calculation.

9. Add a filter to the crosstab to view information about 2017 and modify the periodic aggregated measure.

a. In the canvas, click the crosstab to select it, if necessary.

b. In the right pane, click Filters.

c. Select New filter  Transaction Date.

d. On the left of the filter, drag to Jan2017.

The crosstab should resemble the following:

The _IgnoreInteractiveTimeFrameFilters_ option ignores only interactive filters before the calculation is performed. Filters specified on the Filters pane are not interactive (that is, report viewers cannot modify them).

e. In the left pane, click Data.

f. Right-click Order Total (3M change) and select Edit.

i. For the Time Filters operator on the left of the minus sign, select _IgnoreAllTimeFrameFilters_.

ii. For the Time Filters operator on the right of the minus sign, select _IgnoreAllTimeFrameFilters_.

The expression should resemble the following:

iii. Click OK to apply the changes.

The crosstab should resemble the following:

The _IgnoreAllTimeFrameFilters_ option ignores all filters that are based on the same date data item used in the calculation.

10. Save the report.

Practice 11.4

1. Creating a Periodic Aggregated Measure

a. Open the browser and sign in to SAS Viya.

b. Open the VA2-Practice7.2b report in the Courses/YVA285/Advanced/Practices folder.

c. Create a new periodic aggregated measure (Cumulative Profit) that calculates a running total for Profit over the year.

d. Add Cumulative Profit to the list table.

e. Modify Cumulative Profit to calculate a running total for the fiscal year (starting in March).

The list table should resemble the following:

f. Save the report.

Challenge (Optional)

1. Adding Scopes to an Aggregation

a. Open the browser and sign in to SAS Viya.

b. Open the VA2-Practice7.2b (Challenge) report in the Courses/YVA285/Advanced/Practices folder.

c. Answer the following questions:

What is the expression for Average Salary?

Answer:

What is the average salary for all employees?

Answer:

d. Management would like to see the total salary for all employees in the crosstab. Edit the Average Salary aggregated measure to add the following scope:

Grand total: <total salary for all employees>

Note: All aggregated measures must have a base expression.

Note: Scopes enable you to apply different expressions for different crossings of categories. The expression for each scope is applied only when that exact crossing of categories is displayed in an object. Wherever that exact crossing of categories is not displayed, the base expression is applied.

The crosstab should resemble the following:

e. Save the report.

The Suppress Operator

The Suppress operator hides aggregated values if the specified condition is true. Hidden values are displayed as an asterisk within objects. Suppressed data is commonly used to protect the identity of individuals in aggregated data when some aggregations are sparse. For example, suppose your data contains test scores by school district for various demographics. If one of your demographic categories is represented by only a single student, the value would represent the score for that student. Data suppression hides the test score for the student so that the test score is not publicized. The suppress operator requires several parameters: the suppression condition, the suppressed value, and the suppression application. The suppression condition will determine which rows to hide. In this example, any group that has a count of quantity less than 5 will be hidden. The suppressed value will determine which values to hide. In Figure 11.9, total quantity by group will be hidden if the group contains a count less than 5. All other groups will show the total quantity by group.

Figure 11.9: Aggregated (Advanced) Operators: Suppress

The suppression application specifies whether additional values will be hidden when a single value is hidden for a group or subgroup. The withoutComplement option specifies that only values that meet the suppression condition are hidden, whereas the withComplement option specifies that additional values may be hidden if hidden values can be inferred by totals, subtotals, or other cell values. We will examine these options more in a later demo.

Note: The data item used in the suppression condition and the suppressed value do not have to be the same. For example, you can suppress total profit (suppressed value) for all countries where the number of orders is less than 10 (suppression condition).

When using suppressed data, keep the following best practices in mind:

● Never used the unsuppressed version of the data item in your report, even in filters and ranks. You can hide the unsuppressed version using the Data pane.

● Avoid using suppressed data in any object that is the source or target of a filter action. Filtering can sometimes make it possible to infer the value of suppressed data.

● Avoid assigning hierarchies to objects that contain suppressed data. Expanding or drilling down on a hierarchy can sometimes make it possible to infer the value of suppressed data.

Note: The suppression application parameter can have one of the following values:

withoutComplement	Only values that meet the suppression condition are suppressed.
withComplement	Values that do not meet the suppression condition might be suppressed if the hidden value can be inferred by totals, subtotals, or other cell values.

For more information about aggregated (advanced) operators, see “Reference: Operators for Data Expressions” in the SAS Visual Analytics: Working with Report Data documentation.

Demo 11.5: Creating an Advanced Aggregated Measure

This demonstration illustrates how to create an advanced aggregated measure that suppresses data values based on some condition.

1. From the browser window, sign in to SAS Viya.

2. In the upper left corner, click (Show list of applications) and select Explore and Visualize.

SAS Visual Analytics appears.

3. Click All Reports.

a. Navigate to the Courses/YVA285/Advanced/Demos folder.

mmmmmm. Double-click the VA2-Demo7.2c report to open it.

4. View display rules for the crosstab.

a. In the canvas, click the crosstab object to select it.

b. In the right pane, click Rules.

If Freq is less than 5, the number is red and bold in the crosstab.

5. Create a new advanced aggregated measure, Quantity (Suppress).

a. In the left pane, click Data.

b. Select New data item  Calculated item.

c. In the Name field, enter Quantity (Suppress).

d. For the Result Type field, select Aggregated Measure.

e. On the left side of the window, click Operators.

f. Expand Aggregated (advanced).

g. Double-click Suppress to add it to the expression.

h. Expand Comparison.

i. Drag x<y to the condition area in the expression.

j. Expand Aggregated (simple).

k. Drag Count to the number field on the left of the less than sign.

l. For the Count operator, verify that _ByGroup_ is specified.

m. Right-click the number field for the Count operator and select Replace with  Quantity.

n. Enter 5 in the number field on the right of the less than sign.

o. On the left side of the window, expand Aggregated (simple) on the left side of the window, if necessary.

p. Drag Sum to the number field in the Suppress operator.

q. For the Sum operator, verify that _ByGroup_ is specified.

r. Right-click the number field for the Sum operator and select Replace with  Quantity.

s. For the Suppress operator, verify that withComplement is specified.

The expression should resemble the following:

t. In the upper right corner of the window, for the Format field, click (Edit).

i. In the Format window, verify that Comma is selected as the format.

ii. For the Width field, verify that 12 is specified.

iii. For the Decimals field, enter 0.

iv. Click OK.

u. Click OK to create the new periodic aggregated measure.

The Data pane should resemble the following:

6. Modify the crosstab to use the advanced aggregated measure.

a. In the canvas, click the crosstab to select it.

b. In the right pane, click Roles.

c. For the Measures role, select Quantity  Quantity (Suppress).

The crosstab should resemble the following:

When the frequency is less than 5 (including missing values), quantity values are suppressed. For Bulgaria, the quantity is suppressed for internet/catalog customers but not for Orion Club Gold members.

d. In the right pane, click Options.

e. In the Totals and Subtotals group, select Totals.

The crosstab should resemble the following:

When totals are added, it might be possible to infer a hidden value for a group. The withComplement option hides additional values, so hidden values cannot be inferred.

7. Modify the advanced aggregated measure.

a. In the left pane, click Data.

b. Right-click Quantity (Suppress) and select Edit.

c. For the Suppress operator, select withoutComplement.

The expression should resemble the following:

d. Click OK to apply the changes.

The crosstab should resemble the following:

The withoutComplement option does not hide additional values when a hidden value can be inferred.

8. Save the report.

Practice 11.5

1. Creating an Advanced Aggregated Measure

a. Open the browser and sign in to SAS Viya.

b. Open the VA2-Practice7.2c report in the Courses/YVA285/Advanced/Practices folder.

c. In the list table, replace Order Date with Customer Country.

Note: The Cumulative operator works only with date or datetime data items.

d. Create a new advanced aggregated measure (Aggregate Profit) that calculates a running total for Profit over the countries.

Hint: Use the AggregateCells operator to create the new aggregated measure.

e. In the list table, replace Cumulative Profit with Aggregate Profit.

f. Answer the following question:

What is the total profit for all countries?

Answer:

The list table should resemble the following:

g. Save the report.

Challenge (Optional)

1. Working with the CumulativePeriod Scope Parameter (_ToDate_)

a. Open the browser and sign in to SAS Viya.

b. Open the VA2-Practice7.2c (Challenge 1) report in the Courses/YVA285/Advanced/Practices folder.

c. Answer the following question:

What is the cumulative profit for Dec2012? Does this match the total profit for 2012 in the Yearly list table?

Answer:

d. Edit the Cumulative Period (First Quarter) data item to aggregate profit through the first quarter only.

e. Answer the following question:

What is the cumulative profit for Mar2012? Does this match the new total profit for 2012 in the Yearly list table?

Answer:

f. Save the report.

2. Working with the First Operator

a. Open the browser and sign in to SAS Viya.

b. Open the VA2-Practice7.2c (Challenge 2) report in the Courses/YVA285/Advanced/Practices folder.

c. Create a new aggregated measure (First Month) that calculates the order total for the first month of the year only.

d. Add the new aggregated measure to the Yearly list table.

e. Answer the following question:

What is the order total for Jan2014? Does this match the order total for 2014 in the Yearly list table?

Answer:

f. Save the report.