Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.

Alteryx Designer Discussions

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

Filtering to previous month

richard_gardner
7 - Meteor

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?

10 REPLIES 10
danrh
13 - Pulsar

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!

adm510
11 - Bolide

edit: never mind, I misread the question and thought you wanted the prev month and earlier :)

estherb47
15 - Aurora
15 - Aurora

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

MarqueeCrew
20 - Arcturus
20 - Arcturus

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
SanK
5 - Atom

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.

Baradomas
5 - Atom

@SanK

 

DateTimeAdd(DateTimeFirstOfMonth(),-1,"month")= DateTimeTrim([yourDate],"month")

 

This worked the best, thank you!

Ben_Kohler
7 - Meteor

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... 

MichelleMitchellLutz
8 - Asteroid

This worked like a charm! Thanks for sharing.

SanK
5 - Atom

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

Labels