Advanced Date Filter

[awmeyertimmy]

I'm trying to build a filter within a crosstab to automatically filter the date. 

I have 14 months of data, Dec 16' - Jan 18', but I only want to show data from 4 months ago (This month that would be Oct. 17').  My data set updates each month by rolling forward one month (e.g. my next data set will have dates raning from 1/1/17 - 2/28/18) and I will need the filter to grab data from Nov. 17' only.

I currently have this as the syntax of the filter but it is not working correctly.

DatePart(AppVintage) = DateFromMDY(( Month(DatePart(Now())) - 4 ), 1, Year(DatePart(Now())))

 The format of the date field (AppVintage) is DATETIME22.3

Can someone help me with this issue?

Thanks!

Adam

[ballardw]

I don't use VA so I'm not sure of all the functions that may be available. If you have the INTNX function that will increment date, time and datetime values.

so perhaps

AppVintage = intnx('dtmonth',now(),-4,'B');

which if the function intnx is available should return the time of the beginning day of the month 4 previous.

[awmeyertimmy]

INTNX would work great! However, I don't believe it's available in Visual Analytics.  That's why I'm stuck. Thanks though!

[ballardw]

If a base SAS function such as INTNX or INTCK would be helpful and isn't available in VA you might consider posting a request to add them in the SASWare Ballot section of the forum: https://communities.sas.com/t5/SASware-Ballot-Ideas/idb-p/sas_ideas

[FredrikE · Accepted solution]

Create a numeric meassure from year and month and then use rank to select then highest 4. 🙂 //Fredrik

[awmeyertimmy]

Brilliant! Thanks