Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

formula to find first day of the last month

novice1
8 - Asteroid
hello, My Alteryx workflow shows daily activity for the current month. However on the first of the month it shows a blank data since my data is always day behind. I would like it to show everyday of the previous month on day 1 of the current month. I have the formula to only show the month up to previous day, but my issue is on the 1st of the month. Since I have a relative date set to current month it does not show the previous months data. I either need some way to set the relative date dynamically by looking to see if today is the first and set to previous month or be able to set a relative date to yesterday. This issue is only on the first day of the month. Every other day is fine since the previous day and current month are within the same month. here is the formulas i am using for current month this year and current month last year. how does these need to modified to get me desired results? DateTimeAdd(DateTimeToday(),(DateTimeDay(DateTimeToday())-1)*-1,"days") DateTimeAdd(DateTimeTrim(DateTimeToday(),"firstofmonth"),-1,"years") any help would be much appreciated. thank you
6 REPLIES 6
BenMoss
ACE Emeritus
ACE Emeritus
IF Datetimetoday() = datetimefirstofmonth() then datetimeadd(datetimetoday(),-1,days) else datetimetoday() endif

This will provide you with yesterdays date if it's the 1st of the month, otherwise it will return the date today.

Ben
novice1
8 - Asteroid
thank you. but this still brings me back 01/08/2019. this would work through the month, but not on the 1st day of current month i need to see result for the first day of the previous month if today is 1st of the month so the result i am looking for today is 01/07/2019
ChrisTX
15 - Aurora

Is this what you're looking for?  If not, can you provide some examples?

 

Start Date:

IF DateTimeDay(Datetimetoday()) = 1 then
datetimeadd(datetimetoday(),-1,"months")
else
datetimefirstofmonth()
endif

 

 

End Date:

IF DateTimeDay(Datetimetoday()) = 1 then
datetimeadd(DateTimeLastOfMonth(),-1,"months")
else
DateTimeLastOfMonth()
endif

 

Capture.JPG

novice1
8 - Asteroid
Thank you. I will give a try. This is the query i have built. As i mentionned before it works perfectly during current month. My issue is - i would like it to bring back data for previous month when current day is 1st of any month. I have attached my Workflow. time range in SQL Query is looking at c.GREGORIAN_DATE Between Date '2019-01-01' And Date - 1
novice1
8 - Asteroid
I will try to work around it. i think i need to modify my SQL query. Open to suggestions. Thank you.
Goldrickd
5 - Atom

I know this post was a few years ago now but figured I would offer a solution in case anybody else comes across this issue. You already have the logic in place to determine if it is the first of the month.

 

If you are looking to get the date for the first of the previous month it can be accomplished with this:

 

DateTimeAdd(DateTimeFirstOfMonth(), -1, 'months')

 

Apologies if it has since been solved

 

Labels