community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More

formula to find first day of the last month

Meteor
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
Alteryx Certified Partner
Alteryx Certified Partner
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
Meteor
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
Bolide

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

Meteor
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
Highlighted
Meteor
I will try to work around it. i think i need to modify my SQL query. Open to suggestions. Thank you.
Labels