Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Use Rank in SAS Visual Analytics to display the last date, month or rolling window

Reply
Community Manager
Posts: 463

Use Rank in SAS Visual Analytics to display the last date, month or rolling window

How do you answer specific 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.

"Use rank!" says SAS' Teri Patsilaras in her recent blog post, Use Rank in SAS Visual Analytics to display the last date, month or rolling window.

 

I thought you all would find it useful.

 

Anna

 

 

Contributor
Posts: 37

Re: Use Rank in SAS Visual Analytics to display the last date, month or rolling window

Hi anna,

i have used Rank exactly by following the instructions of SAS Teri Patsilaras bog. However, in my case, because I'm using parallelperiod calculations, the numbers changes and the option doesn't work. Any idea, how can I restrict the window to b displayed on my graph (aside from doing it manually).

 

Here is the full graph:

full graph.PNG

I would like to display only for month after Jan 2016.

 

When using a Rank option to show the last 13 months (below). Values are correct, except that it includes all the Other and extremely high values for the red bar in Jan 2016.

selected graph with rank.PNG

 

when I unticketed the All Other, this was the output:

selected graph with rank unticked all other.PNG

Now, I do not know where the other values in red have gone.

Does anyone have answer or idea what might be happening?

 

 

cheers,

 

rita

Highlighted
Community Manager
Posts: 463

Re: Use Rank in SAS Visual Analytics to display the last date, month or rolling window

Hi Rita,

 

Thanks for your question! I'll do some digging here and either Teri or I will get back to you.

 

Best,

Anna

SAS Employee
Posts: 8

Re: Use Rank in SAS Visual Analytics to display the last date, month or rolling window

Hi Rita,

 

I'd love to help figure out where the red bars representing Tickets Y_1 went.  Are you able to attach some data so that I can test it out? 

 

Thank you,

Teri

Contributor
Posts: 37

Re: Use Rank in SAS Visual Analytics to display the last date, month or rolling window

Hi Teri,

 

Here is an example of the data.

I could also give the details by day, but lets start with this summary data (already aggregated by month). Please note that 3rd column was a aggregated field calculated using parallelperiod.

 

Thanks

 

Cheers,

 

Rita

SAS Employee
Posts: 8

Re: Use Rank in SAS Visual Analytics to display the last date, month or rolling window

Hi Rita,

 

The good news is, I figured out why we are seeing this behavior.  The down side is we might have to change the way you are visualizing your data or add a bit of work to your data preparation before you load it into Visual Analytics.

 

Thank you for the sample data.  I imported the first 2 columns, as I wanted to replicate your issue as closely as possible, and I created an out-of-the-box parallel period.  Then I edited this aggregated measure to essentially take the same month previous year’s value, just as your sample data showed. 

 

01_Rita_AggregatedMeasure.png


Baseline
Here is what your data looks like in a list table.  Notice that I have an additional column, DateByMonthNum, that we will use for the Rank By value to display the last 13 months.

 

02_Rita_BaselineListTable.png


And here is what it looks like as a bar chart:

 

03_Rita_BaselineBarChart.png 

Rank Applied
For comparison, I created another list table and applied the Rank to display the last 13 months:

 

04_Rita_RankAppliedListTable.png

 

Here is what the bar chart looks like with the Rank applied:

 

05_Rita_RankAppliedBarChart.png

 

Success!  I was able to replicate your issue.

 

Why are we seeing this?
We are seeing this behavior because of the way Aggregated Measure Parallel Periods work.  In order for this aggregated measure to evaluate its value, the previous period has to be displayed on the visualization. 

 

This is why when we apply the Rank to display only the last 13 months, the only period where both the current year and previous year are available to calculate the aggregated measure, in our case: January. 

 

Now that we know why we are seeing this behavior, let’s look at your options. 

 

Option 1
Add the parallel period aggregated measure to the preparation of data before your load it into Visual Analytics.  This may not be possible on detailed data and may require you to create a summary table in order for you to properly calculate your aggregated parallel measure. 

 

Option 2
If your aggregated measure is truly only looking at the previous period’s value, and no calculations are needed, then you have a second option.  You would be to use the data you have loaded into Visual Analytics, create a couple of additional calculated data items and adjust your visualization role assignments.  For example:

 

Duplicate your existing date data item and use the Format data property to create a Year and Month data item. 

 

06_Rita_Option2NewCategories.png

 

Then your role assignments would look similar to this:
• Category:  DATE_Month
• Measures:  Count
• Group:  DATE_Year

 

In this option, you do not need to use Rank to display the last 13 months. 

 

07_Rita_Option2BarChart.png


I hope this helps you, Rita. 

 

Just remember, if you want to view a Parallel Period Aggregated Measure – the periods that you want to compare must be visible on the visualization. 

 

Thank you,
Teri

 

Contributor
Posts: 37

Re: Use Rank in SAS Visual Analytics to display the last date, month or rolling window

Hi Teri,

 

Thank you for the comprehensive reply. 

Your final conclusion was what I expected (and feared the most).

 

However, there was a strange behaviour when you ticked All Other, the data was shown almost correctly (it failed for Jan2016 calculations for previous year).

 

And using your final bar graph example, do you have any idea how to display only the 2016 and 2017 bar? I mean ideally, I just want to show the most current data and against the previous year (as a benchmark). If I use filter, this will not work.

 

And could you expand on you Option 1. Would this require extra variables (like items_previous year) to be added to my data?

Option 1
Add the parallel period aggregated measure to the preparation of data before your load it into Visual Analytics.  This may not be possible on detailed data and may require you to create a summary table in order for you to properly calculate your aggregated parallel measure. 

 

thank you so much fort he help!

SAS Employee
Posts: 8

Re: Use Rank in SAS Visual Analytics to display the last date, month or rolling window

Hi Rita,

 

To expand on Option 1, I will show you an example of how you can do this using Visual Analytics.  However; you can use this same technique on any RDBMS (relational database management system). 

 

Essentially, from what you’ve told me, you want two values for each month:  Current Year value and the Previous Year value for the same month. 

 

One way to do this is to:
1) Create a new table, Previous Year
2) Rename the column to Previous Year Count
3) Add one year to each date value
4) Join the new Previous Year table to the original table by date value.

 

Here is a visual of what the two base tables will look like:

 

02_01_Rita_BaseTables.png

 

I created the Previous Year table using Visual Data Builder in Visual Analytics. 

 

02_02_Rita_PreviousYearQuery.png

 

I added both columns from the original table.  I changed the name from Count to PreviousYearCount.  And here is the expression I used to add one year to the DATE_Month column:

 

CASE
 WHEN YEAR(DATE_Month) < YEAR(TODAY()) THEN INTNX (‘year’, DATE_Month, 1, ‘same’)
 WHEN MONTH(DATE_Month) < MONTH(TODAY()) THEN INTNX (‘year’, DATE_Month, 1, ‘same’)
 ELSE DATE_Month
 END

 

02_03_Rita_DATE_Month_Expression.png

 

 

Run the query and your new table will be created. 


Now we need to join this new table, PreviousYear, to the Original table.  You will need to save this as a new table, I named it CurrentAndPreviousYears. 

 

Again, I used a Visual Data Builder query and performed a left join where all the records from the Original table were included and only the matching rows from the PreviousYear table.  The join condition was where DATE_Month equaled DATE_Month.

 

02_04_Rita_CurrentAndPreviousYearsQuery.png

 

And include the columns:
• DATE_Month
• Count
• PreviousYearCount

 

02_05_Rita_Columns.png

 

Now we can use our new CurrentAndPreviousYears table as our data source for our report.  I cleaned up the labeling of the columns and verified the values using a list table:

 

02_06_Rita_Verify.png

 

 

Now let’s create the bar chart:

 

02_07_Rita_BarChartNoRanks.png

And finally follow the steps to add a Rank to display the last 13 months but using the newly calculated data item DateByMonthNum:

 

02_08_Rita_BarChartWithRanks.png


Rita, this gets you exactly the visualization you wanted but, like I mentioned for Option 1, it requires a bit of data preparation.  So long as your data isn’t “huge” you can use this method I’ve outlined using Visual Data Builder.  If you already have a RDBMS in place, you can speak with your DB Admin to help you put similar business logic in place before your data is loaded into Visual Analytics. 

 

And once you’ve finished creating your CurrentAndPreviousYears table; you can drop the Original table and the PreviousYear table from your LASR server to keep things cleaned up.  Also, you may need to contact your SAS Administrator to be sure you have the right Roles and Permissions to perform all those tasks in Visual Data Builder. 

 

One of the huge benefits of this option, is that you can perform this logic at the detailed level of data and use the current year and previous year values in a variety of visualizations.

 

Good luck,
Teri

Contributor
Posts: 37

Re: Use Rank in SAS Visual Analytics to display the last date, month or rolling window

Hi Teri,

 

Thank you so much for the detailed explaination.

 

Cheers

 

Rita

 

Ask a Question
Discussion stats
  • 8 replies
  • 921 views
  • 2 likes
  • 3 in conversation