extract month from date

hi,

i have following formula build into my Alteryx workflow, that always looks at the data for current month and it would pull back data for previous month is today is 1st of the current month.

if DateTimeDay(DateTimeToday()) = 1
then DateTimeAdd(DateTimeAdd(DateTimeToday(),[Date today]*-1,"days"),-1,"month")
else DateTimeAdd(DateTimeToday(),(DateTimeDay(DateTimeToday())-1)*-1,"days") endif

I am struggling to modify the above formula to account for the fact that when i run report on Sunday (and if said sunday is in the new month) how can i get data for previous month with the cut off day previous friday?

what i am trying to do is to get sales data previous month if sunday is in 2nd November, and look at the current month if sunday is on any other day of the current month

Date Time

Accepted answers

messi007

Hello,

Yes correct, and if the first day of the month is Sunday it will give you the first day of the previous month.

Please mark my answer as accepted as a solution with a like if you don't mind 🙂

Happy to help!

Regards,

All comments

messi007

Hello,

You can check first if the day is Sunday and it's the first of the month formula below

DateTimeFormat([DateField],"%A") = "Sunday" and DateTimeDay(DateTimeToday()) = 1

then you update your formula with -2

else your formula.

Hope that will help,

Regards,

novice1

hi,

can you please explain a bit more.

currently i have formula set as

formula to workout date for yesterday

DateTimeDay(DateTimeToday())-1

and then a formula that extracts 1st day of the current month or previous month if today is 1st of the month

if DateTimeDay(DateTimeToday()) = 1
then DateTimeAdd(DateTimeAdd(DateTimeToday(),[Date today]*-1,"days"),-1,"month")
else DateTimeAdd(DateTimeToday(),(DateTimeDay(DateTimeToday())-1)*-1,"days") endif

So, if the day that i am running report is 2nd of the month (sunday for example), how do i have to amend the above formula so that it will look up data for previous month up to and inluding friday

novice1

i have attached part of my workflow

so if i run this today i need to see result as 2020-10-01

month.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703