ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
1 Day Left! - The Alteryx Community will be temporarily unavailable for a few hours due to implementation of the new SSO experience starting tomorrow at 5pm MDT. Please plan accordingly. For more information, read the blog.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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?

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
19 - Altair
19 - Altair

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 reboot. Order shall return.
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... 

Labels