When adding ENDIF it reads as Invalid Type in operate&lt

Question

I am currently trying to use the following formula to get my Quarter:

IF [Month]<=3 THEN "Q1" Elseif [Month] <=6 THEN "Q2" ELSEIF [Month] <=9 THEN "Q3" ELSE "Q4" ENDIF

I currently have the data type at: Int16, but it still doesn't seem to recognise the <=9. As soon as I add the ENDIF the last section from <=9 to the end gets unhighlighted. Is there something I am doing wrong?

SeanAdams · Answer

Hey @MichaelVanFarowe

As @binu_acs and @RobertOdera said - this looks like the inbound type of the month is a string.

This is part of the behaviour of a string which is not obvious

"11" looks less than "3" for strings because strings are sorted by the first character, then the second character - and so you get behaviours that you would not expect unless you are looking for it.

If you can force the type of the fields going into your IF statement (using a Select tool) then you may solve this.

Have a good week

Sean

RobertOdera · Answer

Hi, @MichaelVanFarowe

Kindly consider the following options:

If your [Month] is a string

IF ToNumber([Month]) > 8 THEN "Q4"

ELSEIF ToNumber([Month]) > 5 THEN "Q3"

ELSEIF ToNumber([Month]) > 3 THEN "Q2"

ELSE "Q1"

ENDIF

If your [Month] is a number

IF [Month] > 8 THEN "Q4"

ELSEIF [Month] > 5 THEN "Q3"

ELSEIF [Month] > 3 THEN "Q2"

ELSE "Q1"

ENDIF

I hope you find this helpful - Cheers!

binu_acs · Answer

@MichaelVanFarowe What is the data type of your output field? Make sure it should be string and the Month field should be int