The quickest approach would be to add a binary flag to your data indicating the end of the month. For example:
data want;
set have;
flag_end_of_month = (date = intnx('month', date, 0, 'E') );
run;
You can then use this to create a custom category in Visual Analytics that creates your filter. Or, you can name it 'End of Month' directly in the code.
Alternatively, you can create this flag directly in Visual Analytics with some more manual logic:
if( (month('Date'n) in(1,3,5,7,8,10,12) AND dayofmonth('Date'n) = 31)
OR (month('Date'n) in(4,6,9,11) AND dayofmonth('Date'n) = 30)
OR (month('Date'n) = 2 AND dayofmonth('Date'n) in(28*(1-'Leap Year'n), 29*'Leap Year'n) )
)
return 'End of Month'
else ' '
Where 'Leap Year'n is:
if( (mod(year('Date'n), 4) = 0 AND mod(year('Date'n), 100) > 0) OR (mod(year('Date'n), 400) = 0 AND mod(year('Date'n), 100) = 0) ) return 1 else 0
Which we get from the standard leap year formula:
The year is evenly divisible by 4 and the year is not evenly divisible by 100 or;
The year is evenly divisible by 400 and evenly divisible by 100
Thank you for posting this question - this is a great use case for expanding date/time functions within Visual Analytics to do these types of calculations.
... View more
