Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

gpsnitie
6 - Meteoroid
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
8 REPLIES 8
david_fetters
11 - Bolide

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.

 

Capture.PNG

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
6 - Meteoroid
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
11 - Bolide
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
5 - Atom

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
8 - Asteroid

@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
8 - Asteroid

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
8 - Asteroid

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
8 - Asteroid

Solved my own question.  

 

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

Labels