ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

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

Taking Right 2 digits of integer

RitaB
7 - Meteor

Hello Alteryx Community,

 

I have integer billing dates of 202001, 202002, 202012, 202101, etc.  I want to create a formula to take this billing date and add one month by adding 1.  It works fine for all but December, when I want to take 202012 and make it 202101.  I thought I could write an IF statement that says to add 1, except if the right 2 digits are 12, then add 89.  But the right function seems to only work with strings.  Is there an easy way to accomplish this?  Thank you! 

RitaB_1-1617885474612.png

 

AngelosPachis
15 - Aurora

Hi @RitaB ,

 

Can you try wrapping your [Date] field in a To string function?

 

So your expression should be

 

 

IIF(Right(Tostring([DATE]),2)="12",[date]+89,[date]+1)

 

 

The error you are getting is because the Right function can only be applied to string data types and apparently your [DATE] field is of numeric data type.

RitaB
7 - Meteor

Hi @AngelosPachis , it worked!   thank you.

 

 

Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @RitaB,

 

I'd probably take a slightly different approach with my formula and use a series of datetime functions to convert it into a date format, add the month and convert it back to the original format (YearMonth):

 

 

ToNumber(DateTimeFormat(DateTimeAdd(DateTimeParse(ToString([Date]),'%Y%m'),1,'month'),'%Y%m'))

 

 

The way to read these formulas is from the inside out.

(1) Parse the string to be a date format

(2) DateTimeAdd() to add one month onto date created in the previous step

(3) Parse the date created in the previous step into YearMonth format

(4) Convert to a number (if you want to store the value as a numeric data type - this step isn't needed if the value is stored as a string)

Jonathan-Sherman_0-1617886484157.png

 

I've attached my workflow for you to download if needed.

 

Kind regards,

Jonathan

Labels