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

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