Why Doesnt this work?

[jjdenha]

IF [Length of Service1] <= 90 THEN '0-3 Months' elseif [Length of Service1] > 90 and [Length of Service1] <=180 THEN '3-6 Months' ELSEif [Length of Service1]> 180 and [Length of Service1] <= 365 then '6-12 Months' elseif [Length of Service1] >365 and [Length of Service1] <= 1095 then '1-3 Years' else if [Length of Service1] >1095 and [Length of Service1] <= 2190 then '3-5 Years' elseif [Length of Service1] >2190 and [Length of Service1] <= 3650 then '5-10 Years' elseif [Length of Service1] > 3650 and [Length of Service1] <= 5475 then '10-15 Years' else '15+ Years' ENDIF

[DavidP]

Problem highlighted in blue  - remove the space.

[jjdenha]

Why is it returning a Null?

[AbhilashR]

@jjdenha - can you modify the [Length of Service 1] fields datatype to DOUBLE and test? If you still get a null, could you share a screenshot of the [Length of Service 1] field which is causing the null?

[jjdenha]

Screen shot is above

[AbhilashR]

@jjdenha - screenshot didnt come through. Can you upload it again?

[jjdenha]

I did away with the 'and' and just used < for all of them problem solved

[tonypreece]

I was going to suggest just a simple less than each time instead of (greater than x and less than y).

The formula runs from left to right, so if the first test is matched, the formula can stop processing. This means if LoS is <=90 it stops at the first test, Alteryx doesn't go on to test if it is ALSO less than 180.

If it isn't <=90, but it happens to be <=180 that condition wins then the formula doesn't move any further, and so on until the first occurrence of a test being passed, or the final else if none are passed.