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

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

 

3 REPLIES 3
AngelosPachis
16 - Nebula

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