alteryx formula

[novice1]

Hi,

I have the following formula that i that brigs back data for current month and bring back data for last month if today is 1st of the month. 

I need to modify this formula to do exactly same, but for the previous year.

This is the formulas I use

DateTimeDay(DateTimeToday())-1

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

Any help would be very much appreciated 

Thank you

[LordNeilLord · Accepted solution]

Hey @novice1 

This might work:

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

[novice1]

Hi,

Perfect thank you. i was trying that but was getting parse error. Looks likeI forgot to add  another DateTimeAdd in my statement.

Thank you so much for quick response.

[TonyA]

I think this will do what you want. Let me know if I'm missing something:

IF DateTimeDay(DateTimeToday()) = 1
THEN DateTimeAdd(DateTimeAdd(DateTimeFirstOfMonth(),-1,"month"),-1,"year")
ELSE DateTimeAdd(DateTimeFirstOfMonth(),-1,"year")
ENDIF

By the way, you found a pretty neat trick for getting to the first of any month, but if you need the first of the current month, you can use the DateTimeFirstOfMonth() function as I did above..

EDIT: I found a mistake in the second part of the expression that I fixed above.

[novice1]

Thank you. This solution works as well as the one suggested by Lord Neil.

I will have to trial it tomorrow. my hope is , that tomorrow it will only bring back data for Current Month, i.e. October 2019 and October 2018.

Have a great day.

[novice1 · Accepted solution]

Hello,

So i have tried and tested following formulas for the salt 2 days and it works like a charm.

I use this formula to work out date today

DateTimeDay(DateTimeToday())-1

and then apply this formula to pull back data for previous month if today is 1st of any given month

if DateTimeDay(DateTimeToday()) = 1

then DateTimeAdd(DateTimeAdd(DateTimeToday(),[Date today]*-1,"days"),-1,"month")

else DateTimeAdd(DateTimeToday(),(DateTimeDay(DateTimeToday())-1)*-1,"days") endif

and use formula below to apply same principle for Last year

if DateTimeDay(DateTimeToday()) = 1

then DateTimeAdd(DateTimeAdd(DateTimeAdd(DateTimeToday(),[Date today]*-1,"days"),-1,"month"), -1, "years")

else DateTimeAdd(DateTimeAdd(DateTimeToday(),(DateTimeDay(DateTimeToday())-1)*-1,"days"), -1, "years") endif

Regards.