BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
juluka
SAS Employee

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!

 

Snapshot of Frequency of source_date Month grouped by source_date Year 03-24-2022 at 10.13.13 AM.png

1 ACCEPTED SOLUTION

Accepted Solutions
acordes
Rhodochrosite | Level 12

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 )pic.png

 

 

 

View solution in original post

2 REPLIES 2
acordes
Rhodochrosite | Level 12

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 )pic.png

 

 

 

juluka
SAS Employee

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.

 

custom graph.PNG

 

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!

 

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 619 views
  • 0 likes
  • 2 in conversation