Filtering to previous month
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Good Afternoon. I'm trying to create a dynamic filter that will filter the date/time to only the previous month. The data comes in as DateTime. I'm sure this is simple, but I can't seem to come up with a solution that works. Suggestions?
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You could use a Custom Filter with the following, where [Dt] is your date field:
DateTimeFormat([Dt],'%m%y')=DateTimeFormat(DateTimeAdd(DateTimeToday(),-1,'month'),'%m%y')
EDIT: Just to clarify, I'm including the year portion in case you're looking at data that covers more than the last 12 months. If you have more than 12 months worth of data and compare to just the month, you'll end up getting all data that happened in July, regardless of whether it was July of this year, last year, or 10 years ago. Hope this helps!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
edit: never mind, I misread the question and thought you wanted the prev month and earlier :)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Richard,
Custom filter is the way to go. If you're only concerned with the month, then drop the %y from the above solution.
Here's my suggestion:
DateTimeFormat([date],"%b")=DateTimeFormat(DateTimeAdd(DateTimeNow(),-1,"month"),"%b")
This pulls the month out of your Date field, and compares it to the month before (the DateTimeAdd function here subtracts one month from today's date).
When the month turns over, this will still work to capture the previous month. Hope it helps!!
Best,
Esther
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi all :)
DateTimeDiff(DateTimeTrim(DateTimeStart(),"Month"),DateTimeTrim([Tran Date],"Month"),"Month")
That formula will count the number of calendar months between when the workflow starts and the transaction date. 0 is returned for the current month and 1 for the prior month.
If I wanted something equal to last calendar month in a filter, it would be:
DateTimeDiff(DateTimeTrim(DateTimeStart(),"Month"),DateTimeTrim([Tran Date],"Month"),"Month") = 1
The formula reads as:
Calculate the difference in MONTHS between the start date in the format of: YYYY-MM-01 00:00:00 and the transaction date in the same format.
I like it...
Cheers,
Mark
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Here is a very similar way to EstherB47's method, but taking care of the year as well.
DateTimeAdd(DateTimeFirstOfMonth(),-1,"month")= DateTimeTrim([yourDate],"month")
You never know, eventually mutiple years will creep in and stuff up your logic.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@SanK
DateTimeAdd(DateTimeFirstOfMonth(),-1,"month")= DateTimeTrim([yourDate],"month")
This worked the best, thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Wonder if I can use this same formula in order to get the previous month of data from a year prior. I'm assuming I would just input "-1"year" after that...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This worked like a charm! Thanks for sharing.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
DateTimeLastOfMonth() picks up the last day of the month.
If today is the 17th of Feb 2022, then
DateTimeLastOfMonth() gives you 2022-02-28 (Date formated)
DateTimeTrim(DateTimeLastOfMonth(),"month") gives you 2022-01-01
