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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.9

The latest release includes several enhancements designed to improve your Community experience!

Learn More
SOLVED

alteryx formula

Meteor

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

 
Alteryx Certified Partner

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

Meteor

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.

 

 

Highlighted
Alteryx
Alteryx

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.

Meteor

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.

 

Meteor

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