VA Report Example: Use Display Rules to get diagonal coloring on a Crosstab

1 Like

I saw a SAS Visual Analytics report requirement request a diagonal color pattern be applied on a Crosstab Object. And since I just finished my series about Display Rules, I thought this would be a fun exercise to walk through.

Display Rule Series:

Here was the original request:

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

And here is the solution using SAS Visual Analytics:

Data Structure

I wanted to use the Crosstab Object in SAS Visual Analytics for two reasons:

I didn’t want to use a List Table Object because I would have had to add 25 individual data items to the Data Roles.
I wanted to find a mathematical pattern or equation that I could use to assign the Display Rules and I didn’t want to define four Display Rules for each of the 24 individual data items.

Therefore, I transposed data structure similar to this. Notice that when I import this Excel file into SAS Viya, the Date-1 will come in as a measure data item and the Date will come in as a category data item.

In order for me to assign both Date-1 (measure) and Date (category) to the Crosstab Object they must both be category data items. In SAS Visual Analytics, it is very easy to do this. All you need to do is:

Duplicate the Date-1 data item.
Change the Classification from Measure to Category.
Change the name to make it easier for you to identify.

This allows me to define the Crosstab Object Data Roles like this:

Display Rule Solution

I got lucky with the data structure that was in the original request. We can see in the Row and Column Data Roles that the values are from 0 to 23 or a character prefix in front of 0 to 23.

This gave me the idea much like the elementary math story of Gauss, where he was given the task to add up the numbers between 1 and 100 inclusive and instead discovered a formula to solve the problem.

Now, in no way am I comparing my abilities for mathematical problem solving to Gauss, but I did see a pattern in the coloring when I used the expression of taking the absolute value of the difference in the Row and Column values.

One of the tricks to get this to work is that I need both data items to be measures. Funny, right! Because I just made sure that both data items were categories. So now we need Date (category) to be a measure data item. To do this, we have a few more steps than simply duplicating and changing the classification type.

Here is the expression to use. We will use the Parse Operator to assign a numeric format but we first need to get only the numbers after the prefix "t1_". I will use the Substring Operator to start at the fourth position and read two digits.

Now I can build the expression for the Color Expression where I take the absolute value of the difference between the Row and Column values.

In order to define a Display Rule for our Color Expression data item, we will need to add it to the Crosstab Object. I know this isn’t ideal, but the Crosstab Object does not support the Hidden Data Role. Don't worry, I’ll show you a few tricks we can apply to polish off the solution. Add Color Expression to the Crosstab.

Now here is the pattern I found to use in building the Display Rules. If you need additional guidance on the steps then please take a look at my blog: SAS Visual Analytics Display Rules: Table – Level.

At this point, you’ll have something that looks like this:

Finishing Touches

Let’s make the a few changes so that this can get closer to the initial customer requirement.

Rename Color Expression to “.”

Use the properties drop-down to change the name of the Color Expression data item to be a single small character. In the end I chose to use the period, but a lower case L could also work. Don’t worry, SAS Visual Analytics automatically handles any expressions or Display Rule references to this name and it’s updated automatically for you!

Rename Date-1 (Cat) to Date-1

Again, don’t worry that it looks like we have two data items with the same name Date-1. SAS Visual Analytics will add an internal reference name so that it can tell the difference between the two.

Style the Crosstab Cells

Last, we need to color all of the cells in the Crosstab to be pink using the Background color and I deselected the Alternating background color.

Now we have our finished visualization:

Conclusion

I have shown how we can use the Crosstab Object and Display Rules to get a diagonal coloring on the visualization. There are a few workarounds to set up the report but, in the end, this is a great way to programmatically reproduce the initial requirement while being able to easily schedule data refreshes.

See the below for additional Visual Analytics report examples:

Find more articles from SAS Global Enablement and Learning here.