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.
SOLVED

alteryx formula

novice1
8 - Asteroid

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

 
5 REPLIES 5
LordNeilLord
15 - Aurora

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
8 - Asteroid

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
Alteryx Alumni (Retired)

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
8 - Asteroid

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
8 - Asteroid

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. 🙂

 

Labels