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

Alteryx designer Discussions

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

Subtraction in if statement

Meteor

Hi, 

 

I am trying to create a new field using the formula tool where I take my accounting date and subtract one month to get the production month. However I am getting the error "Invalid type in subtraction operator". What am I doing wrong with the formula below? 

 

IF [Accounting Date]>=2 AND [Accounting Date]<=12
THEN [Accounting Date]-1
ELSEIF [Accounting Date]=1
THEN [Accounting Date]="12"
ELSE "Error"
ENDIF

Alteryx Partner

Based on your formula, it looks like your dates are stored as strings rather than numbers. In your second "THEN" statement, you have quotes around 12, meaning that it is the string "12" rather than the number 12. You cannot subtract numbers from a string.

 

The easiest way to get around this is to add a select tool before your formula and change the type to Int.

 

Let me know if this helps,

 

Cheers!

Highlighted
Asteroid

Hi @AustinBauer

 

As Tcroberts mentioned you likely want to double check your data types. After doing so, try this:

 

IF [Accounting Date]>=2 AND [Accounting Date] <=12
THEN [Accounting Date]-1
ELSEIF [Accounting Date]=1
THEN 12
ELSE 0
ENDIF

 

You can't insert a string like "error" into a numeric field. Same for your value of "12" if you want accounting date to equal 12. Just return it like I stated above. 

 

Let me know how this works. 

 

Thanks,

Cameron

Nebula
Nebula

Hi Austin

 

This looks like a datatype issue.  In the THEN [Accounting Date]-1  line, you're treating [Accounting Date] like a number by trying to add 1.  In THEN [Accounting Date]="12" you're treating it like a string by comparing it to the string "12". One of these two will fail depending on the type of [Accounting Date]

 

Also, THEN [Accounting Date]="12" will return true or false since you're returning the result of comparing [Accounting Date] and "12".  If you're trying to assign 12 the the line should be THEN "12"

 

Dan

ACE Emeritus
ACE Emeritus

It's hard to tell what exactly is going wrong without an example, but I suspect that your  Even so, your IF statement is mixing data types and is incomplete.

 

If your [Accounting Date] field only contains the current month (e.g. "09") but that value is a string, your formula will also fail. If you wanted this situation to work, the proper syntax would be:

 

IF TONUMBER([Accounting Date])>=2 THEN TOSTRING(TONUMBER([Accounting Date])-1)
ELSEIF [Accounting Date]="1" THEN "12"
ELSE "Error"
ENDIF

Another issue could be that your [Accounting Date] field is just text, not a Date or Date/Time format. Even if it is a properly formatted Date or Date/Time format, you have to use the special DateTime formula tools.

 

If your [Accounting Date] field contained today's date '2018-21-09' you can create a new [Production Date] field with the formula DateTimeAdd([Accounting Date],-1,'month'). The nice thing about this is that subtracting the month in January will also handle the previous month being in the previous year.

 

 

 

Labels