Teri Patsilaras

3月 132018

SAS Visual Analytics 8.2 introduces the Hidden Data Role. This role can accept one or more category or date data items which will be included in the query results but will not be displayed with the object. You can use this Hidden Data Role in:

  • Mapping Data Sources.
  • Color-Mapped Display Rules.
  • External Links.

Note that this Hidden Data Role is not available for all Objects and cannot be used as both a Hidden Data Role and Data tip value, it can only be assigned to one role.

In this example, we will look at how to use the Hidden Data Role for an External Link.

Here are a few applications of this example:

  • You want to show an index of available assets, and you have a URL to point directly to that asset.
  • Your company sells products, you want to show a table summary of product profit but have a URL that points to each Product’s development page.
  • As the travel department, you want to see individual travel reports rolled up to owner, but have a URL that can link out to each individual report.

The applications are endless when applied to our customer needs.

In my blog example, I have NFL data for Super Bowl wins. I have attached two columns of URLs for demonstration purposes:

  • One URL is for each Super Bowl event, so I have 52 URLs, one for each row of data.
  • The second URL is for each winning team. There have been 20 unique Super Bowl winning teams, so I have 20 unique URLs.

Hidden Data Role in SAS Visual Analytics

In previous versions of SAS Visual Analytics, if you wanted to link out to one of these URLs, you would have to include it in the visualization like in the List Table shown above. But now, using SAS Visual Analytics 8.2, you can assign a column containing these URLs to the Hidden Data Role and it will be available as an External URL.

Here is our target report. We want to be able to link to the Winning Team’s website.

In Visual Analytics 8.2, for the List Table, assign the Winning Team URL column to the Hidden Data Role.

Then, for the List Table, create a new URL Link Action. Give the Action a name and leave the URL section blank. This is because my data column contains a fully qualified URL. If you were linking to a destination and only needed to append a name value pair, then you could put in the partial URL and pass the parameter value, but that’s a different example.

That is using the column which has 20 URLs that matches the winning team in the Hidden Data Role. Now, what if we use the column that has the 52 URLs that link out to the individual Super Bowl events?

That’s right, the cardinality of the Hidden Data Role item does impact the object. Even though the Hidden data item is not visible on the Object, remember it is included in the results query; and therefore, the cardinality of the Hidden data item impacts the aggregation of the data.

Notice that some objects will just present an information warning that a duplicate classification of the data has caused a conflict.

In conclusion, the Hidden Data Role is an exciting addition to the SAS Visual Analytics 8.2 release. I know you'll enjoy and benefit from it.

The power behind a Hidden Data Role in SAS Visual Analytics was published on SAS Users.

12月 222017

Another report requirement came my way and I wanted to share how to use our Visual Analytics’ out-of-the-box relative period calculations to solve it.

Essentially, we had a customer who wanted to see a metric for every month, the previous month’s value next to it, and lastly the difference between the two.

Relative Period Report in SAS Visual Analytics

To do this in SAS Visual Analytics, which is available in versions 7.3 and above, use the relative periodic operators. I am going to use the Mega_Corp data which has a date data item called Date by Month using the format: MMMYYYY. SAS Visual Analytics supports relative period calculations for month, quarter and year.
The first two columns, circled in red, are straight from the data. The metric we are interested in for this report is Profit.

Next, we will create the last column, Profit (Difference from Previous Period), which is an aggregated measure that uses the periodic operators.

From the Data pane, select the metric used in the list table, Profit. Then right-click on Profit and navigate the menus: Create / Difference from Previous Period / Using: Date by Month.

A new aggregated measure will be created for you:

If you right-click on the aggregated measure and select Edit Aggregated Measure…, you will see this relative period calculation, where it is taking the current period (notice the 0) minus the value for the previous period (notice the -1).

Okay – that’s it. This out-of-the-box relative period calculation is ready to be added to the list table. Notice the other Period Operators available in the list. These support SAS Visual Analytics’ additional out-of-the-box aggregated measure calculations such as the Difference between Parallel Periods, Year to Date cumulative calculations, etc.

Now we have to create the final column to meet our report requirement: the Previous Period column.

To do this we are going to leverage the out-of-the-box functionality of the relative period calculation. Since this aggregated measure calculates the previous period for the subtraction – let’s use this to our advantage.

Duplicate the out-of-the-box relative period calculation by right-clicking on Profit (Difference from Previous Period) and select Duplicate Data Item.

Then right-click on the new data item, and select Edit Aggregated Measure….

Now delete everything highlighted in yellow below, remember to also delete the minus sign. And give the data item a new name. Click OK. This will create an aggregated measure that will calculate the previous period.

The final result should look like this from either the Visual tab or Text tab:

Now we have all the columns to meet our report requirement:

Now that I’ve piqued your interest, I’m sure you are wondering if you could use this technique to create aggregated data items to represent the Period -1, -2, -3 offset? YES! This is absolutely possible.
Also, I went ahead and plotted the Difference from Previous Period on a line chart. This is an extremely useful visualization to gage if the variance between periods is acceptable. You can easily assign display rules to this visualization to flag any periods that may need further investigation.

Relative Period Report in SAS Visual Analytics was published on SAS Users.

11月 142017

SAS Visual Analytics 7.4 has added the support for date parameters. Recall from my first post,  Using parameters in SAS Visual Analytics, a parameter is a variable whose value can be changed at any time by the report viewer and referenced by other report objects. These objects can be a calculated item, aggregated measure, filter, rank or display rule. And remember, every time the parameter is changed, the corresponding objects are updated to reflect that change.

Here is my updated table that lists the supported control objects and parameter types for SAS Visual Analytics 7.4. The type of parameter is required to match the type of data that is assigned to the control.
Notice that SAS Visual Analytics 7.4 has also introduced the support for multiple value selection control objects. I’ll address these in another blog.

Using Date Parameters in your SAS Visual Analytics Reports

Let’s look at an example of a SAS Visual Analytics Report using date parameters. In this fictitious report, we have been given the requirements that the user wants to pick two independent date periods for comparison. This is not the same requirement as filtering the report between a start and end date. This report requirement is such that a report user can pick two independent months in the source data to be able to analyze the change in Expense magnitude for different aggregation levels, such as Region, Product Line and Product.

In this example, we will compare two different Month,Year periods. This could easily be two different Quarter,Year or Week,Year periods; depending on the report requirements, these same steps can be applied.

In this high level breakdown, you can see in red I will create two date parameters from data driven drop-down lists. From these parameter values, I will create two calculated data items, shown in purple, and one aggregated measure that will be used in three different report objects, shown in green.

Here are the steps:

1.     Create the date parameters.

2.     Add the control objects to the report and assign roles.

3.     Create the dependent data items, i.e. the calculated data items and aggregated measure.

4.     Add the remaining report objects to the canvas and assign roles.

Step 1: Create the date parameters

First we will need to create the date parameters that will hold the values made by the report viewers. From the Data Pane, use the drop-down menu and select New Parameter….

Then create your first parameter as shown below. Give it a name.

Next, select minimum and maximum values allowed for this parameter. I used the min and max available in my data source, but you could select a more narrow range if you wanted to restrict the users to only have access to portions of the data, just so long as the values are in your data source since, in this example, we will use the data source to populate the available values in the drop-down list.

Then select a current value, this will serve as the default value that will populate when a user first opens the report.

Finally, select the format in which you want your data item to be formatted. I selected the same format as my underlying data item I will be using to populate the drop-down list.

Notice how your new parameters will now be available from your Data Pane.

Step 2: Add the control objects to the report and assign roles

Next, drag and drop the drop-down list control objects onto the report canvas. In this example, we are not using the Report or Section Prompt areas since I do not want to filter the objects in the report or section automatically. Instead, I am using these prompt values and storing them in a parameter. I will then use those values to create new calculated data items and an aggregated measure.

Once your control objects are in the report canvas, then use the Roles Pane to assign the data items to the roles. As you can see from the screenshot, we are using the Date by Month data item to seed the values of the drop-down list by assigning it to the Category role, this data item is in our data source.

Then we are going to assign our newly created parameters, Period1Parameter and Period2Parameter to the Parameter role. This will allow us to use the value selected in our calculations.

Step 3: Create the dependent data items, i.e. the calculated data items and aggregated measure

Now we are free to use our parameters as we like. In this example, I am prompting the report viewer for two values: Period 1 and Period 2 which are the two periods the user would like compared in this report. So, we will need to create two calculated data times from a single column in our source data. Since we want to display these as columns next to each other in a crosstab object and use them for an aggregated measure, this technique can be used.

Calculated Data Item: Period 1 Expenses

From the Data Pane, use the drop-down menu and select New Calculated Item…. Then use the editor to create this expression: If the Date by Month for this data row equals the parameter value selected for Period 1, then return the Expenses; else return 0.

Calculated Data Item: Period 2 Expenses

Repeat this using the Period2Parameter in the expression.

Aggregated Measure: Period Difference

Next, we want to calculate the difference between the two user selected Period Expenses. To do this, we will need to create an aggregated measure which will evaluate based on the report object’s role assignments. In other words, it will be calculated “on-the-fly” based on the visualization.

Similar to the calculated data items, use the Data Pane and from the drop-down menu select New Aggregated Measure…. Use the editor to create this expression. Notice that we are using our newly created calculated data items we defined using the parameter values. This expression does not use the parameter value directly, but indirectly through the calculated data item.

Step 4: Add the remaining report objects to the canvas and assign roles

No that we have:

  • our Control Objects to capture the user input.,
  • the Parameters to store the values.,
  • and the Calculated Data Items and Aggregated Measure created…

we can add our report objects to the canvas and assign our roles.

You can see I used all three new measures in the crosstab object. I used the aggregated measure in the bar chart and treemap but notice the different aggregation levels. There is even a hierarchy assigned to the treemap category role. This Period Difference aggregated measure calculation is done dynamically and will evaluate for each visualization with its unique role assignments, even while navigating up and down the hierarchy.

Here are some additional screenshots of different period selections.

In this first screenshot you can see the parallel period comparison between December 2010 and 2011.

In these next two screenshots, we are looking at the Thanksgiving Black Friday month of November. We are comparing the two years 2010 and 2011 again. Here we see that the Board Product from the Game Product Line is bright blue indicating an increase in magnitude of Expenses in the most recent period, Nov2011.

By double clicking on Board in the treemap, we are taken to the next level of the hierarchy, Product Description, where we see a the largest magnitude of Expenses is coming from Backgammon and Bob Board Games.

In these final two screenshots we are comparing consecutive periods, November 2011 with December 2011. We can see from the bar chart easily the Region and Product Line where there is the greatest increase in Expenses.

I’ve configured a brush interaction between all three visualizations so that when I select the tallest bar it will highlight the corresponding data values in the crosstab and treemap.


Now you can use date parameters in your Visual Analytics Reports. There are several applications of this feature and this is only one way you can use parameters to drive business intelligence. Using this technique to create columns based on a user selected value is great when you need to compare values when your source data isn’t structured in this manner.

Using Date Parameters in your SAS Visual Analytics Reports was published on SAS Users.

7月 262017

In a previous blog, I describe how there are a few new features related to report and page prompts in SAS Visual Analytics 8.1; namely the ability to configure cascading prompts in VA 8.1: Cascading Prompts as Report and Page Prompts.

In this blog, I will cover how to configure prompts, either report, page, or report canvas prompts, that use different data sources.

Different Data Sources with overlapping data values

First, you must have two different data sources added to your Visual Analytics report. These data sources must have values that overlap that you wish to prompt on. All of the values do not need to map, but they must have some values in common if you wish to use a shared prompt.

In this example, we will prompt for Product Line. Let’s examine the column values:

I’ve color coded the values that I would like to map together. I see that the only values that match “out-of-the-box” is Game.

One work around to get all of the values to match will be to create a Custom Category and use that column for the mapping.

In a “real world” scenario, this may not be ideal. The cardinality of the two columns may be so large that you may have to go back to either the source data or ETL job to produce better matching values.

However, if you are using date columns as the mapping columns things are considerably easier as year, month, and quarter are standard values that match without extra steps.

Here is my new Custom Category that I will use for my mapping:

Here are my mappings now. I will be using Product Line (New) for the Insight Toys data source moving forward.

Add prompts

There are two different locations where you can add prompts, i.e. Control Objects, which means there are two different ways to configure prompts with different data sources:

1.     Report and Page Prompts

2.     Report Canvas Prompts

Report and Page Prompt configuration for different data sources

For this first example, I will configure a Button Bar object placed in the Page Prompt area to filter two different data sources. For the Button Bar’s Category Role, I will use the data source with the largest available selection, in this case, the Product Line (New) from Insight Toys.

Now let’s configure this button bar to filter both data sources. You must activate the button bar by clicking on it, then right-mouse click and select Edit data source mappings

Then you simply have to pick your source table’s column to map to your target table’s column.

That’s it. The mapping is complete. Here is what the report would look like with different selections made for the button bar. Notice, that since I used the Insight Toys data source for the Role assignment, and it has more values than available in the Mega Corp data. If a selection is made where nothing matches in Mega Corp, as in the Gift example, then the Mega Corp bar chart is blank.

Report Canvas Prompt configuration for different data sources

In this second example, I am going to use a List Control object within the report canvas to filter two different data sources. Again, I will use the Insight Toys’ Product Line (New) column as the List Role Category assignment since it has the most values.

Now to configure the list to filter both bar charts. Click on the list control object to activate the window. Then select the Actions pane, and use the Add button to select Add filter.

Then select both bar charts as the target of the filter Action.
Next, select the Map data option.

Select the source data’s column to map to the target data’s column. Use the + to add additional column mapping criteria.

Here is how the report would look with a few of the values selected from the list table. You can see how both Mega Corp and Insight Toys display overlapping values for Product Line but for any unique Product Lines, such as Gift, its values are only displayed on the Insight Toys bar chart.

Now you know how to configure your control objects for multiple data sources. This works no matter how many data sources you add to your report, simply use the Map data option and select the mappings between the source data and target data.

As I mentioned earlier, a frequently used application of mapping prompts for multiple data sources is for date columns. Here is a screenshot of one example using year and month. I also styled the button bar’s selected background and text color to coordinate with the graphs.


SAS Visual Analytics 8.1: Configuring prompts with different source data was published on SAS Users.

6月 022017

There are several exciting new features in SAS Visual Analytics 8.1 that I know will excite you, including that you can now configure cascading prompts for the Report Prompt and Page Prompt areas! Prior to SAS Visual Analytics 8.1 there was a way to use parameters to configure cascading prompts for report and section prompts, but now all we have to do is define a Filter from the Actions Pane. It’s a lot less work and there are even a couple of different ways it can be done.

Method 1

1.      Add Control Objects to Report or Page Prompt area.
2.      Assign Roles to Control Objects.
3.      Define a Filter from the Actions Pane.
4.      Test your cascading prompts and enhance controls.

Method 1, Step 1
Add the desired Control Objects to either the Report or Page Prompt areas.

Method 1, Step 2
Assign Roles to your Control Objects.

Method 1, Step 3
Create the cascading prompt by defining a Filter from the Action Pane. This means that for the selected data value in the source object, it will use that value to filter the target, i.e. return the rows where source prompt = selected value.

In this example, we are using our SASHELP Cars data set. Our source is the car’s Origin and the target is the car’s Make. If you pick Europe for Origin it will return the corresponding car Makes such as Audi, BMW, Saab, Volkswagen, etc.

Click on the source Control object, Origin, and from the Actions Pane, select Add filter.

Next, select the target object to filter and click OK.

You can check to be sure your action is defined by clicking on the Objects and looking at the Actions Pane.

Method 1, Step 4
Enhance your controls using the Options Pane and then test out your configured cascading prompts!

In my example, I made the Origin button bar required and changed the Selection background color and text color. I also added a bar chart to my report.

Method 2

1.      Define a prompt hierarchy.
2.      Drag the prompt hierarchy to the Report or Page Prompt area.
3.      Test your cascading prompts.
4.      (Optional) Enhance controls.

Method 2, Step 1
Create a hierarchy for the data items for which you wish to create cascading prompts.

Method 2, Step 2
Drag your prompt hierarchy to either the Report or Page Prompt area.

Method 2, Step 3
Test your automatically configured cascading prompts! That’s right – this is another new feature. Well, it’s two combined, first is the Auto Controls. Second, if you drag a hierarchy data item to either the Report or Page Prompt area, SAS Visual Analytics automatically defines the Filter Actions for you.

Method 2, Step 4 (Optional)
Go back and enhance your Control Objects using the Options Pane as you like.

There you have it – cascading prompts anywhere in SAS Visual Analytics 8.1 reports: in Report Prompts, Page Prompts and of course still anywhere in the report canvas; and it’s as easy as defining Filter Actions. You may be wondering, is it possible to configure cascading prompts for different data sources? Not to worry – it is and I’ll show you in in my next blog.

Cascading Prompts as Report and Page Prompts in SAS Visual Analytics was published on SAS Users.

4月 112017

You may be wondering if you need something special to gain access to the Schedule Chart object. Don’t worry, you don’t, you just need to unhide this visualization if it isn’t already. You can do this from the Objects’ drop down menu. There are several other objects available to you if you’d like to show those as well. Simply check the ones you want to include in the list and then click ok.

The VA 7.3 Schedule Chart is similar to the traditional Gantt chart in that it serves to illustrate the start and finish duration of a category data item. You must provide a category data item and two date or datetime data items representing the start and end dates. You can also add a group by category, lattice by columns and/or rows.   Here is a simple example that visualizes my local school district’s 2016-2017 Traditional School Calendar.

Schedule charts can be used to visualize a variety of data such as:

  • Calendar Events
  • Project Tracking
  • Campaign/Promotional Runs
  • Floor Service Coverage

Essentially, any category which can be associated with a start and end date can use this visualization.

Here are some examples of using the Schedule Chart to look at project tracking data. Our team uses a similar visualization; however, I have modified real names and gave it a Star Wars theme for a bit of fun.

Example 1

In this example, the Project name is assigned as the main category. Here are a few takeaways:

  • The schedule chart gives a great bird’s eye view of a lot of data. This particular data has over 350 projects spanning a team of 19 individual members.
  • The schedule chart automatically includes the least and greatest date value. You can override the X Axis in the Properties tab by assigning a fixed minimum and maximum.

In this next screenshot, I have selected the manager Obi-Wan Kenobi to filter the Schedule Chart. Therefore, by adding section filters to this report, you can see how spotting coverage of Projects and Project Types are easy. And, with some Mock Combat planned later in the year, the Jedis might want to up their training.

Example 2

This example uses the same Schedule Chart role assignments as before, but different section prompt filters. Here, this report shows how an individual team member can use the Schedule Chart to visualize several things:

  • A list of his/her assigned projects.
  • The planned duration of each project.
  • How the projects are spread throughout the year.

If you chose to look at a particular Project Type, then this visualization would help list the Project names and how they span across the year.

Example 3

The next example moves away from the traditional use of putting the Project as the main category data item and instead places the Team Lead on the Y Axis. This now allows us to see how busy each Team member is and with which Project Type. By the way, did you notice the neat way the Team names are sorted? I used a custom sort!

Here are just a few more things you can do to enhance this visualization: you can adjust the transparency so you can see overlapping projects and you can easily add reference lines to the X Axis. In this example, I’ve added the reference lines for Q1 through Q4. I’ve also selected a manager from the report section filters and added an additional data item for the Label.

Example 4

In this example, I wanted to demonstrate the use of the Lattice rows and I also applied a Display Rule for the Project Type. This is a good way if you want to view overlapping information and the transparency property isn’t distinct enough.


Schedule Chart Limitations

If you want a bar representation on the Schedule Chart to appear then the data must have a start and end date for every row of data. If either is missing, then the category name will appear on the Y Axis but no bar will be displayed on the visualization.   Also, you cannot create an interaction from or to the Schedule Chart object. That means you cannot create a filter or brush with another object in the report area. The Schedule Chart will be filtered by either report or section prompts.   I hope you can include the Schedule Chart into your reports, it is one of my favorite visuals.

SAS Visual Analytics Designer 7.3 Schedule Chart was published on SAS Users.

2月 142017

In this post I wanted to shed some light on a visualization you may not be using enough: the Word Cloud. Word association exercises can often be a fun way to pass the time with friends, or it can trigger immediate action – just think of your email inbox and seeing an email from a particular person: your boss, wife, husband or child. The same can be true for information for your organization. A single word can quickly, efficiently and effectively communicate the performance of a company’s metric, hence the value of using a word cloud visualization in your report.

Let’s look at some examples. Here I am using the Insight Toy data and looking at the performance of Products based on customer orders.

As the word cloud in SAS Visual Analytics 7.3 Designer has a maximum row return of 100, I have used the Rank feature to look at the top 25 Products and the bottom 25 Products. I also created a filtered interaction between the word clouds and their respective list tables below to show a bit more detail around the next level in the hierarchy after Product Make.

Notice how impactful these Product names are compared to when using their corresponding SKUs. Be sure to pick a meaningful category to represent your data in the word cloud.

This type of visualization could lead to a great comparison report, comparing what the top and bottom Products were for the same month in the previous year.

What if your data doesn’t have the appropriate column to display on a word cloud? No problem. In this next example, I took the value of Sales Rep Rating and created a new Calculated Data Item to represent values less than or equal to 25% to be Poor, inclusively between 26% and 50% to be Average and everything else to be Above Average.

Using a word cloud for this new category data item allows you to quickly move through the different states and compare the Sales Rep Performance frequency. You could also use this new category to compare each performance group’s Order Totals.

Here is California’s Sales Rep Performance:

And here is Maryland’s Sales Rep Performance:

These are two ideas for you to think about how you might include the word cloud visualization into your reports to help quickly and effectively represent the status of a company’s metric beyond the standard text analytics usage.

tags: SAS Professional Services, SAS Visual Analytics

Visualization Spotlight: Visual Analytics Designer 7.3 Word Cloud was published on SAS Users.

11月 152016

If your SAS Visual Analytics report requirements include linking out to separate reports without the need to pass values, you may want to consider using images to enhance the appearance of your base report. Here are three style examples using images that you can use depending on your report design requirements and report user preference:

1.     Visually appealing
2.     Generic
3.     Screenshot of actual report.

There is no better substitute for looking at examples so here are some screenshots for you:

1.     Visually appealing

Use images in SAS Visual Analytics

2.     Generic


3.     Screenshot of actual report


Image selection

Using an image in your report has never been easier. You can navigate your local machine for the image and, if you want, you can also save the image in metadata. This allows other users with access to that metadata location the ability to use the same image. This is great when you want to impose consistency throughout your reports.



Setting link using image

To define a report link from your image, click on your image then open the Interactions tab. Then use the New drop-down menu and select the type of link you wish to define. For a Report Link or Section Link, use the Browse button to navigate the metadata and select your target. If you are linking to an External URL then enter the fully qualified URL or you can define a link to a stored process.


Here is a breakdown of the report objects used in the main dashboard report. I also included the screenshots of my Daily, Weekly, and Monthly report examples.

Dashboard example breakdown


Daily report example


Weekly report example


Monthly report example




tags: SAS Professional Services, SAS Visual Analytics

Use images in SAS Visual Analytics to enhance your report link was published on SAS Users.

10月 252016

Requirements that are the most easily described can often be the most difficult to implement. I’m referring to requests like:

  • Display a gauge with the most recently collected metric.
  • Plot a 18 month rolling window of profit.
  • Display last month’s products percent of total metrics for visual comparison.

Okay, so these are pretty specific requests, which I built a report to answer, but none the less, requirements like these do exist.

Use Rank in SAS Visual Analytics

So, how do you implement these requests? Use rank! You might be wondering how this is possible since the rank feature requires a numeric value and these requirements are based on dates. Solution: use the TreatAs function. Let’s break it down step by step.

But first, here is a breakdown of the report objects used in this report. Notice that this report contains a section prompt via a button bar which prompts the user to select a Product Line. This section prompt filters all of the other objects by that Product Line value.


Step 1: Use TreatAs to create a metric from your date category

I am assuming that your data source has a date category. This will work with a date or date by month or date by year formatted data item. So long as the data item is recognized as a date then this technique will work.

This example will use the Date by Month data item. We will use the TreatAs function to create a metric, or in other words, a numeric representation of the date. That’s the great thing about dates in SAS, they simply represent the number of days before or after January 1, 1960. So the most recent the date, the larger the number, which we can then use rank to order.

From the Data tab, use the drop-down menu and select New Calculated Item….


Give your new calculated data item a name.

The result type will be numeric.

Under Operators, use the search window to find the TreatAs function; then drag that onto the visual pane. For the drop-down option, select _Number_.
Finally, drag the date data item onto the visual pane. In this example, we are using Date by Month


Step 2: Change the aggregation on your new measure to be non-additive

Next, we need to make sure this new metric that represents the Date by Month date is non-additive. We will not get the proper result if this new metric takes the sum or average when displayed on a visualization. To do this, navigate to the Data tab and click on the name of the new metric you created. In my example, I created a new metric named DateByMonthNum.

Toward the bottom of the Data tab are the data properties. Under the Aggregation property use the drop-down menu and select one of the non-additive metrics such as: Minimum, Median, or Maximum.


Step 3: Verify that your new measure returns the correct results

Now we can verify that when we rank our new measure, we get the expected results. To do this, I used a list table and added both the date data item Date by Month and the new metric data item DateByMonthNum. Here we can see that when I sort the metric data item by descending I get the expected results where each Date by Month value gives me a different DateByMonthNum value. I can also see that the more recent Date by Month value pairs to a larger DateByMonthNum value.


To be sure that you properly assigned a non-additive aggregation type, you can use the Show detail data property from the Properties tab. At the detail level you should see the same value pairs for the date and metric data items. Once you de-select Show detail data you should see the exact same value pairs. If you do, then you have correctly assigned your non-additive aggregation type.


Step 4: Use Rank to meet report requirements

Now that we have our metric properly created, we can use the Rank feature to display the last month’s metrics or a rolling window.

Last Month’s Metrics
In this visualization I used the Gauge Object.


On the Roles tab, I assigned Profit to the Measure role and Product to the Group role. I then created a five interval Display Rule between 0% and 50% at 10% intervals where anything over 50% is grouped together under the darkest green rule.


Now we must filter this visualization to display only the last month’s profit metrics; we do this by using the Rank feature. From the Ranks tab, you must first select the category data item you wish to subset by the rank. In our example, we want to display the last month’s metrics, so we will want to add a rank for the Date by Month data item. Once selected, click the button Add Rank.


Next we will need to select the metric we want to rank by. Next to the By drop-down; select our newly created metric DateByMonthNum. Then we will want to select the type of rank and how many to return. In this example, we will return the Top Count, i.e. the greatest value. And for the Count we want to return 1.


To help with the titling of the report, I added the exact same rank to a List Table object to display the data’s last month and to help report users know which month they are looking at.



Rolling 18 Month Window
The next visualization I created was a Line Chart Object plotting a rolling window of 18 month profit.


On the Roles tab, I selected Date by Month as the Category and Profit as the Measure.
On the Ranks tab, I selected the same values as I did for the list table and gauge objects, except I selected a Count of 18 to return the top 18 values of Date by Month ranked on our newly created metric DateByMonthNum. The rank will return the top 18 highest values for DatebyMonthNum which pair to the most recent 18 values for Date by Month giving us a rolling 18 month window.


Other Applications

In this example I used Rank at the month level but you could use this technique at the day level, quarter level, essentially for any supported date interval.

Assuming you have the proper data collected, you could also use Rank for the standard use of ranking the top X performing products, sales representatives or investment funds. You could also use rank to identify your bottom performing manufacturing equipment, car mileage, or school ratings.

Other Report Screenshots



tags: SAS Professional Services, SAS Programmers, SAS Visual Analytics

Use Rank in SAS Visual Analytics to display the last date, month or rolling window was published on SAS Users.

9月 062016

For many people, building something from scratch, no matter how simple or complex, is fascinating. That’s why programs similar to How’s It Made are so appealing and, for me, addicting. And thus, the inspiration for this blog; I will walk you through building a set of graphs and how to improve each visualization through my own personal iterative process. Like all forms of art, a visualization is never complete, as constant improvement, tweaking and alterations are required to accommodate the constant influx of data and the ever changing needs of our audience.

These graphs use telecom data about cell phone network service including call duration and data usage.

Example 1: Calls versus Drops

In this first example, I noticed that the data contained the number of calls and the number of dropped calls. Like most analytics, audiences are interested in the outliers. In this case, we look at the poor performing occurrence of a call being dropped. This data would prove useful if a company wanted to research poor performing cell technology either of the handset itself or of the cell towers. It could also be used to find any dead zones, where additional towers may need to be added. In this example, I decided to plot the data against the 24-hour day to determine if volume of calls impacted the number of calls dropped.

Example 1: Iteration 1
Naturally, I started with a bar chart visualization. I plotted the hours of the day (24-hour scale) on the x-axis and the number of calls and the number of dropped calls on the y-axis. At first glance, it looks like there is some variation in the number of dropped calls and the hour of the day.

Visualize cell phone data in SAS Visual Analytics

Example 1: Iteration 2
Since the number of calls and the number of drops are of the same scale, we can easily take the ratio of the two to plot the call drop rate. The equation is to take the number of dropped calls divided by the number of calls. I used an aggregated measure to create this ratio, which will be evaluated on the fly, depending on the Group By variable. In this example the “ByGroup” is the hour of the day, and I used a Percent format.
Visualize cell phone data in SAS Visual Analytics2

This now gives us one bar to evaluate against the hours of the day. We can see that the Call Drop Rate does not fluctuate as much as the previous graph could lead one to believe.

I also added a reference line at 5% to make it easier to see which hour of the day fell below or above the 5% rate.

Visualize cell phone data in SAS Visual Analytics3

Example 1: Iteration 3
Finally, I noticed that the data contained a Cell Technology category. I thought it would be interesting to see if a certain technology was more unreliable than another. To do this, I added Cell Technology to the graph. I liked the visualization the best when I changed the bar chart to a horizontal orientation, used a row lattice for the Cell Technology and kept the 5% reference line. This now gives me an enhanced “quick glance” comparison ability to see that the 4G Cell Technology seems to have the most consistently high Call Drop Rate (over 5%) for all hours of the day.
Visualize cell phone data in SAS Visual Analytics4

Example 2: Call Duration

Example 2: Call Duration
In this second example, I used the Voice_Seconds data item to study the duration of calls over the course of the 24-hour day. This visualization could help determine what the peak hours of the day are for voice calls and potentially the best time to schedule any required maintenance to impact the least amount of customers.

Example 2: Iteration 1
Again, I stared with a bar chart visualization where I plotted hours of the day on the x-axis and the Voice_Seconds on the y-axis. The first thing I noticed was that at hour 20 there was a peak _SUM_ of over 3 million Voice_Seconds. This immediately prompted me to want to find out how long 3 million seconds was and that I need to look at the average of Voice_Seconds.

Visualize cell phone data in SAS Visual Analytics5

Example 2: Iteration 2
The first thing I did was create another aggregated measure to produce the Average Call Duration. To do this I took the sum of Voice_Seconds divided by the sum of the number of calls for a By Group.

Visualize cell phone data in SAS Visual Analytics6

The next thing I wanted to do was provide a reference point for how long 1,000 seconds is in minutes. Granted, I could convert Voice_Seconds into a new metric but instead I decided to use a reference line where 900 seconds equates to 15 minutes.

Visualize cell phone data in SAS Visual Analytics7

Example 2: Iteration 3
Lastly, I wanted to see if the type of Cell Technology had any impact on the distribution or length of call, mostly just because I was curious.  I was surprised that this data shows an average call of 15 minutes. That’s a long personal call when I consider most of my calls consist of “are you on your way?” and “we forgot x at the store – please pick it up on your way home”.

If this were call center data you would be able to determine how quickly issues were getting resolved. If this were sales call data, and representatives were following a script, this visualization would show, on average, how long those calls took and maybe the longer calls would result in a sale. So you could see which hours of the day sold more product.

Visualize cell phone data in SAS Visual Analytics8

Example 3: Data Usage

In this third example, I explored the data usage. I had two data items available for use: mbytes_up and mbytes_down. This visualization could help determine peak hours for which to perform system upgrades or maintenance. It could also help identify those peak hours and then add tower locations to help determine if additional hardware could help network speed performance.

Example 3: Iteration 1
I started with the bar chart visualization and plotted the hours of the day on the x-axis and the mbytes_up and mbytes_down on the y-axis. Again, the first thing I noticed was that I was looking at the _SUM_ for the metrics and the large difference in the numbers for up versus down usage.
Visualize cell phone data in SAS Visual Analytics9

Example 3: Iteration 2
The first thing I did was convert the megabytes to gigabytes by creating new calculated data items and then I created the Average Upload and Download by creating aggregated measures.

Here are the two metrics I created for Upload:
Visualize cell phone data in SAS Visual Analytics10

And here are the two metrics I created for Download:

Visualize cell phone data in SAS Visual Analytics11

This makes the visualization a bit easier to consume, now that we can compare the average upload or download size per session. I also added two reference lines at 5 GB and 15 GB. I still felt like the data needed to be visualized a bit better to understand the usage since I know most typical cell phone plans allow for 5 GB of data per month and the average session using more than 15 GB, it just seems like a lot.

Visualize cell phone data in SAS Visual Analytics12

Example 3: Iteration 3
To further classify the data I added Cell Technology to the visualization and broke it into two visualizations: one for upload and one for download. Once I did that, the visualization really started to show different data usage patterns.

Both visualizations show the 4G technology doesn’t even reach 5 GB, which makes me think that the customers with new phones and new service plans are sticking to their data allowance. But the customers with the older technology of 3G may be “grandfathered” in with their unlimited data plan and making the most of it.

Average Upload (GB)
Visualize cell phone data in SAS Visual Analytics13

Average Download (GB)

Visualize cell phone data in SAS Visual Analytics14

This blog has taken you through three examples of how I iteratively develop visualizations using SAS Visual Analytics. Ultimately, what this process shows you is that the more specific business question you have, the better a visualization you can create.

tags: SAS Professional Services, SAS Visual Analytics

Steps to visualize cell phone data in SAS Visual Analytics: Can you hear me know? was published on SAS Users.