Solved! Go to Solution.
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!
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?
@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.
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).
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.
Solved my own question.
[DateTime_Out] > DateTimeAdd(DateTimeNow(),-1,"months")