How to create a time series plot with percentage of total by month

[xiluzhang]

Hello, I want to plot the percentage of total appts that are completed, canceled, no show, or left without seen by month (see the crosstab) in a time series plot. For crosstab graph, this works as I can calculate a Percent of Row Total - Sum as a measure.

However, SAS VA is not allowing me to plot this same calculated measure in a time series plot. In a time series chart (see pic), when I plot Frequency Percent, it is taking the percent of the grand total, and not the total by month, and I cannot seem to change how this percentage is calculated.

Do I really have to create a custom calculation to get to the line chart I want? In Tableau, you could easily select how you want percentages are calculated for each graph...

Thank you!

[VDD]

It is hard to tell with your example, because you have not supplied your SAS code.

My first subjection would be that you may need a by month statement in your proc freq.

[xiluzhang]

hello, thank you for your response! I am only doing this in Viya Visual Analytics so I did not use code to create the chart. I feel that this is a very basic functionality of a visual analytics software so am hoping that a solution is available. Thank you!

[Sam_SAS · Accepted solution]

Hello,

I am not sure if there is an easy solution for time series plots, but you can easily display this information as a stacked bar chart if you set the Group Scale option to "Normalize groups to 100%".

Getting a similar result in a time series plot might be tricky. However, it should be possible to pre-calculate your percentages as part of your data preparation.

It seems like the same "Normalize groups to 100%" option could be made available in more objects. I'll ask the development team if this would be possible in a future release.

Sam

[Sam_SAS]

I should add, a bar chart does not have to be stacked to use the normalize to 100% option. You might find it clearer in the non-stacked version.

[xiluzhang]

Hi Sam,

Thank you for the quick response! The normalized bar chart suggestion is a good interim solution! The only issue I have is that one of the month's category sorting is different than the others (09/2017). I tried right clicking on the APPT_STATUS legend and sorting ascending or descending, but it only changes the last month. I also tried custom sort for that category, but still did not change the order for that month.

Yes if the developers could work on this for a future release that would be great! Not to constantly compare Viya VA to Tableau as I understand that they each have their pros and cons, but Tableau allowed calculations for any measure to occur within any chart. You just had to indicate whether you wanted it calculated by row, column, whole table, etc.

Thank you!

[Sam_SAS]

Try right-clicking on the chart (not on an axis heading) and managing the sort that way. Sometimes this is an easier way to get the sort you want.

[xiluzhang]

Hmm I do not see the option to sort when I right click anywhere on the chart, and the sorting function isn't working quite right. It may be something specific to our Viya as it was deployed through a consulting company. But I will try this tip once we upgrade later this month. Thank you!

[Sam_SAS]

I believe there was a change to sorting between 8.2 and 8.3. The feature might not be there in 8.2 or earlier.

Right click the "Month of Contact" axis heading and select Sort > Ascending. That should give the desired result. I believe the chart in your screenshot is sorted by Frequency, which is harder to see because of the normalization to 100%. You will see the months are all out of order.

[jakeorcutt]

This would be a welcome update to SAS Visual Analytics as I am wanting to do the same thing with a Time Series plot. I wish I could normalize to 100%.

The problem with using a bar-chart in lieu of a time-series plot is that when I add a 'date' category to the x-axis it doesn't make the x-axis labels look pretty, especially with many weeks of data. Right now I have 100+ weeks of data to plot, so my date labels won't even appear on the x-axis (which you can see in my screenshot).

[edo333]

I have been looking for the same functionality.  

I was hoping that a 100% area chart would be a solution, but I cannot seem to find that option, so you just get stacks that reflect the volume.  I suppose for any given period you could precalculate the total and then create new variables that calculate the percentage of the total for each interval and then display these percentages on the area chart.  

Alternatively there are d3 charts that could be used in a data driven content object to give the exact look and feel to the graph.  

I will experiment with a few option and see what can be done.  In the meantime if someone else has discovered a solution I would be interested in hearing it as well. 

[MVras]

I'm just going to bring up this old issue.

A temporary fix until SAS comes up with a solution is to create measure variables for your groups and then afterwards create aggregated percentage measures and use them instead of your grouping. Now, this will not work if you want an opportunity to filter the variables. E.G. you have to visualize all the lines at all times for the correct number.

Specifically you have to create 8 new variables:

- Number for "Canceled"

- Number for "Completed"

- Number for "Left without seen"

- Number for "No show"

- Percentage for "Canceled"

- Percentage for "Completed"

- Percentage for "Left without seen"

- Percentage for "No show"

Your number variables are created either in data preparation or in VA by creating a new calculated measure. Use the boolian option.

EXAMPLE for your data where I call the variable Canceled_measure:

"If type = "Canceled"

then 1

else 0"

Make a variable like that for all your groups.

Afterwards make an aggregated measure for your percentage:

SUM(Canceled_measure)/SUM(frequency). (both by group)

Make a variable like that for all your new measures.

Now drag your new percentage variables into the measurement datapoint on the graph and get rid of the grouping. I hope it makes sense