Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
SOLVED

Need Dynamic EOMonth Date

Highlighted
5 - Atom

Good morning -

 

First post here.  So let me know if I do anything incorrect.

 

I have a sales fact table with daily dates.  I'm trying to filter out all the days from the most previous month-end to current.  For example:  Today is 12/9.  I want to return all the dates from (12/1 to 12/9).  I've tried a handful of similar forum posts but nothing seems to work correctly. 

 

Is this normally this difficult in Alteryx or is their a simple solution that is eclipsing me?

 

Kind Regards,

Jim

Highlighted
14 - Magnetar
14 - Magnetar

You should be able to do this in 2 tools (or possibly even 1)! See attached solution illustrating both!

 

2 tool option (if you have today's date already in your data somewhere):

1. Formula tool to derive the first day of the month: DateTimeTrim([TodayDate],"month")

2. Generate Rows tool to create a record for every date between StartDate and TodayDate, using StartDate as your Initialization point and then DateTimeAdd([Date],1,"day") to iterate until you get to TodayDate

 

1 tool option (will generate all dates without an input if you're just wanting to do this every time based on today's date when workflow is run):

1. Generate Rows tool starting with DateTimeTrim(DateTimeToday(),"month") to get you start of the month based on today's date, and then same process as above, using DateTimeToday() as your ending condition.

 

Take a look at the attached - hope that helps! 

Cheers! 

NJ

 

 

Highlighted
Alteryx
Alteryx

In @NicoleJohnson 's workflow, you can also use DateTimeFirstOfMonth() in the initialization of the one tool solution. This gives you the first day of the current month.

17 - Castor
17 - Castor

Hi @JaKael02 

 

Another option here is to use a filter tool that is configured to pass only the dates that are in the current month

 

DateTimeMonth([Date]) = DateTimeMonth(DateTimeToday())
And 
DateTimeYear([Date]) = DateTimeYear(DateTimeToday())

 

"Is the month of your date field [Date] the same as today's month"  Same for the year

 

w.png

 

Dan 

Labels