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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

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

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

 

 

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