SAS Visual Analytics Sorting Months that Roll over Years in Line Chart

[juluka]

SAS Visual Analytics V. 8.5

I have a dataset that appends a new monthly snapshot to previous months on the fourth of every month.

I am looking at comparing values for a given month with previous years. I converted the date of snapshot variable to months format (numeric 1 - 12) and placed on a line graph grouping by year. For a given month this provides me with my year-over-year comparison. However, I am just looking for the past six months. Current month (March) would roll to previous year. I'm wondering how I would sort months to be in order but roll to the next year. Proper sort would be as follows: (Oct., Nov., Dec., Jan., Feb., Mar.)

I looked at custom sorts, but the sorting would change depending on the given month and it isn't as simple as sorting in reverse order by month.

The month variable is a date category formatted by month (so numeric values 1 - 12.) Any advice on how to automate sorting to be rolling six months is much appreciated!

[acordes · Accepted solution]

Finally solved it.

apply this filter:

IF ( Month('date_sel'p) >= 'back_sel'p )
RETURN ( Month('monyear'n) BetweenInclusive(( Month('date_sel'p)
- ( 'back_sel'p - 1 ) ), Month('date_sel'p)) )
ELSE ( ( Month('monyear'n) BetweenInclusive(1, Month('date_sel'p)
) ) OR ( Month('monyear'n) BetweenInclusive(( 12 - ( (
'back_sel'p - 1 ) - Month('date_sel'p) ) ), 12) ) )

Create custom graph by overlaying line chart and bar char. 

Join them on category but not on group.

IF NOT(( Month('monyear'n) BetweenInclusive(( Month('date_sel'p)
- ( 'back_sel'p - 1 ) ), Month('date_sel'p)) ))
RETURN Month('monyear'n)
ELSE ( Month('monyear'n) + 12 )

[juluka]

Hi @acordes,

Thanks for this response! I spent some time this morning following along and think foundationally I am there. If anyone has any advice on formatting, the only thing I am still struggling with is selecting data tip values.

For anyone following along at home, one additional thing to note. Instead of using our advanced filters, if you were so inclined you can use the calculated numeric item for months and do a ranking to just get the past 'x' months using either hardcoded, controls, or parameters for user input.

One last thing that I tried (and failed to get working) was creating a calculated item using the parallel period operations. I could never get it to return a nonzero / nonempty value. If anyone knows how to use that I would think that would be a simpler solution.

Thanks again!