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

Datetime string minus one month

wenyg
8 - Asteroid

Hi, how to minus one month from "effectivedate format" column and then compare them using "reversing?" column?

Should I add in one more column?

Or is there a better way to narrow down into one formula?

Thanks

9 REPLIES 9
ShankerV
17 - Castor

Hi @wenyg 

 

To answer your first question, you can datetimeadd([Fieldname],-1,"months") to minus one month from the date.

 

FinnCharlton
13 - Pulsar

Hi @wenyg , 

To minus one month from your effective date column you can replace the formula with

 

datetimeformat(

datetimeadd(

datetimeparse(tostring([effectivedate]),"%Y-%m-%d"),

-1,"month"),

"%Y-%m")

 

 

ShankerV
17 - Castor

Hi @wenyg 

 

You can do it in one formula, but for this both columns should be in date datatype.

 

if [Dateformat]>datetimeadd([Effectivedate format],-1,"months") then "No"
elseif [Dateformat] = datetimeadd([Effectivedate format],-1,"months") then "YES"
ELSE "ignore"
endif

 

 

binuacs
20 - Arcturus

@wenyg One way of doing this

binuacs_0-1674558779904.png

 

ShankerV
17 - Castor

Hi @wenyg 

 

Hope you are expecting the result like this.

 

ShankerV_0-1674559621092.png

 

Input was:

ShankerV_1-1674559645432.png

 

IF DateTimeparse([Dateformat],'%Y-%m')> datetimeadd(DateTimeparse([Effectivedate format],'%Y-%m'),-1,"months")
THEN "No"
ELSEIF DateTimeparse([Dateformat],'%Y-%m')=datetimeformat(datetimeadd(DateTimeparse([Effectivedate format],'%Y-%m'),-1,"months"),"%Y-%m-%d")
THEN "YES"
ELSE "Ignore"
ENDIF

 

ShankerV_2-1674559691812.png

 

 

Many thanks

Shanker V

 

wenyg
8 - Asteroid

Hi Sanker, the formula you write does work for the original data you put, but does not apply when I switch to 2023 - 01 data. The logic I want to apply here is

1) if dateformat is the same as effective date format less than one month then yes - but this equal clause doesn't seem to work.

2) if dateformat is earlier than effective date format less than one month then no,

for example, 11-2022 should be the recognised as "No" if the effective date is 2023-01, and 12-2022 should be recognised as "Yes". Can you take a look if there is anything wrong with my formula?

wenyg_0-1674740383766.png

 

ShankerV
17 - Castor

Hi @wenyg 

 

Please find the modified formula to get the expected output.

 

ShankerV_1-1674761281259.png

 

Many thanks

Shanker V

 

 

ShankerV
17 - Castor

Hi @wenyg 

 

I have created the formula as per the initial screenshot attached,

Now corrected the below to get the desired output.

 

ShankerV_0-1674761497777.png

 

Many thanks

Shanker V

wenyg
8 - Asteroid

Thanks, this works!

Labels