SAS Visual Analytics: Using an expression-based parameter to drive a dynamic reference line

[#1]

In this post, I will create a dynamic reference line for a line chart using expression-based parameters. For this example, I will use the SASHELP.ORSALES table. The table contains data over total retail prices, profit of different products over different quarters. I have created a line chart displaying Total Retail Price in USD by Quarter:

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

I now want to add a reference line that changes dynamically with the data. Specifically, the reference line should be the average of Total Retail Price in USD over all quarters. To achieve this, I first create an expression-based parameter calculating this. In the Data pane on the left, click on New data item and Parameter:

Enter the following expression:

AggregateTable(_Avg_, Table(_Sum_, Fixed('Quarter'n), 'Total Retail Price in USD'n))

Note: If I had only one row per quarter, I could have used a more simple expression:

Avg(ForAll, Profit)

However, here there are multiple rows per quarter. The AggregateTable function takes the sum of all the rows per quarter, and then averages over those sums.

Click OK.

Now we can add the reference line based on this parameter. With the line chart selected, in the Options pane on the right, expand the Reference Lines section and click on + New reference line:

For Value, click on the down arrow and select Average of Total Retail Price. I also changed the line style and color to make the reference line more distinguishable:

Conclusion and Further Reading

This is only one example of how to use expression-based parameters to drive a dynamic reference line.  For a deep dive example of using the AggregateTable function and dynamic references lines, see this article:  SAS Visual Analytics: Use a parameter to drive a dynamic reference line. For more information see the SAS Documentation: Working with Parameters in Reports.

Find more articles from SAS Global Enablement and Learning here.