topic Re: formula to find first day of the last month in Alteryx Designer Desktop Discussions

formula to find first day of the last month

novice1 — Thu, 01 Aug 2019 11:11:07 GMT

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

Re: formula to find first day of the last month

BenMoss — Thu, 01 Aug 2019 11:34:26 GMT

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

Re: formula to find first day of the last month

novice1 — Thu, 01 Aug 2019 11:53:32 GMT

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

Re: formula to find first day of the last month

ChrisTX — Thu, 01 Aug 2019 13:36:14 GMT

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

Re: formula to find first day of the last month

novice1 — Thu, 01 Aug 2019 14:00:39 GMT

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

Re: formula to find first day of the last month

novice1 — Thu, 01 Aug 2019 14:05:26 GMT

I will try to work around it. i think i need to modify my SQL query. Open to suggestions. Thank you.

Re: formula to find first day of the last month

Goldrickd — Wed, 15 Mar 2023 18:11:12 GMT

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