If And Statement with Diffrent Parameters and Error

[CSIMO]

Can someone take a look at this and tell me what I may have missed, my apologies I am new to Alteryx.

IF [OPTION]="Option 1" and [Amount of Sales]<="499.99" and [DATE OPEN]<="2016-08-31"  Then "9.95"
ELSE "NOTHING"
ENDIF

Error I am receiving says "The formula "billing" resulted in a string but the field is numeric. Use ToNumber(…) if this is correct.

Option column is Text

Date open is converted to Alteryx date

Amount of sales is "Double"

[fmvizcaino]

Hi @CSIMO ,

You have three problems in your statement there:

IF [OPTION]="Option 1" and [Amount of Sales]<="499.99" and [DATE OPEN]<="2016-08-31"  Then "9.95"
ELSE "NOTHING"
ENDIF

Since [Amount of Sales] is a number, you can use 499.99 only, without quotes.

For dates, you can't compare using < or >, in that case, you need to use the DatetimeDiff function, as below:

DatetimeDiff("2016-08-31",[DATE OPEN],'days')>0 

The formula is doing "2016-08-31"- [DATE OPEN] , so if this value is higher than 0, the clause is doing the same as you are.

best,

Fernando Vizcaino

[CSIMO]

Thank you Fernando for the reply, I am still seeing the same error. Have I made the corrections correctly Below?

IF [OPTION]="Option 1" and [Amount of Sales]<= 499.99 and DateTimeDiff("2016-08-31",[DATE OPEN],'days')>0 Then 9.95
ELSE "NOTHING"
ENDIF

[fmvizcaino]

One more detail then. Maybe you formatted the Data Type of the column [billing] as numeric

Best,

Fernando V.

[CSIMO · Accepted solution]

 I think I am home thank you Fernando, I had to also change the 'Data Type' to String