How to create dynamic date filter like last 3 months , year to date, rest of the year etc?

[gpsnitie]

Hi,
I want to create a dynamic date filter which automatically chooses last 3 months basis current date in date filter app in Alteryx.

Ex. If today is 10th June 2017 , then it should choose 1st March 2017 to 31st May 2017.

Thanks

[david_fetters]

You can use the DateTimeNow input and DateTimeAdd function to create your filter date, and then either append it to all of your data and run a filter, or feed the resulting filter date into a filter node contained within a macro.  The append can be expensive with a huge amount of records, but complexity vs speed is dependent on your data set.

Basically, just use DateTimeNow to get the current date, then use DateTimeAdd([DateTimeNow], -3, 'month') to get a date that is 3 months ago.  After that, either append it to your data and compare the new FilterDate with the existing date column, or feed it into a batch macro as a control parameter (and having that control parameter replace part of a filter equation) before running your data through the macro.

If that's unclear, let me know and i'll build out a better example!

[gpsnitie]

Thanks for the reply.

Shouldn't we have in date filter itself some ways to filter
last or next n days/weeks/months/year compared to a set date.

Wouldn't that be great?

[david_fetters · Accepted solution]

There is a DateTimeNow() function that you can use in the body of the
filter itself. And now that you mention it I'm kind of ashamed i didn't go
there first!

[FurlongL]

Is there a way to pass in a date value from your process (I would be passing the max date from data set), then set the filter to use x periods before?

[joona_rauhamaki]

---

@FurlongL wrote:

Is there a way to pass in a date value from your process (I would be passing the max date from data set), then set the filter to use x periods before?

---

Hi,

I am also looking for a solution to this problem. I have two data sets with partially overlapping data, and I'd like to use the max or min value of one of the fields in data set A as a filter for a field in data set B. I know I could take the outer join, but I feel it is somewhat inelegant.

[Lisa_M]

I know this is a somewhat aged query - but I had this issue and the thread came up while searching for an answer.  Adding this at the end of the SQL query worked for me: 

To_Date(To_Char(REPORTING_PERIOD), 'YYYYMM') Between Trunc(Add_Months(SysDate, -3), 'MM') And Last_Day(Add_Months(SysDate, -1))

This gave me a rolling three months, and shows the complete months prior to the date the workflow is run - so if it's run mid February, I get November, December and January data.  Additionally, my users don't them have to change dates in the workflow (which has led to issues previously).

[papalow]

Thanks for this suggestion.  I have been able to use the DateTimeNow() in a filter.  When I try to adjust the custom filter amount to subtract 30 days

[DateTime_Out] = datetimenow()-30

I get the following message in the filter configuration pane

Formula: invalid type in subtraction operator

Is there something obvious that I am overlooking in my formula?

Thank you.

[papalow]

Solved my own question.  

[DateTime_Out] > DateTimeAdd(DateTimeNow(),-1,"months")