Kirix Support Forums

iif statements in a calculated field

Please post any help questions, requests or other feedback here.

iif statements in a calculated field

Postby scbret on Fri Aug 15, 2008 11:44 am

I'm trying to make a calculated field give me a "Year-Quarter" (ex 2007-4) string with iif statements using a ship_date field.

Str(Year(Ship_date)) + "-" + iif(Str(Month(Ship_date)) <= "3","1", iif(Str(Month(Ship_date)) >= "4" AND Str(Month(Ship_date)) <= "6", "2", iif(Str(Month(Ship_date)) >= "7" AND Str(Month(Ship_date)) <= "9", "3", "4")))

All work except for Quarter 4 months, they come back like 2007-1 instead of 2007-4.

Could somebody show me what I'm doing wrong?

Thanks
scbret
Registered User
 
Posts: 10
Joined: Tue Jun 24, 2008 9:13 pm

Re: iif statements in a calculated field

Postby Aaron on Fri Aug 15, 2008 2:08 pm

Ok, the problem here is that you're converting the month to a list of characters, also called a string, before performing the comparison. Let's work through the logic for a particular Ship_date in the fourth quarter, December 1, 2007.

For this particular date, Month(Ship_date) returns 12, where 12 is a number, and Str(Month(Ship_date)) returns "12", where "12" is a string. Now, when the formula evaluates, "12" as a string is compared with "3" as a string; and as a string, "12" is less than "3", since for strings, the individual characters of the string are compared from left to right when evaluating equality and "1" is less than "3". To see another example of this, create a calculated field which numbers the rows with RECNO(), convert the row numbers to strings with STR(RECNO()), then sort this calculated field. You'll see that the row numbers as strings follow a similar order: 1, 10, 11, ..., 2, ...

To fix your formula, compare the month of the ship_date to a number within each of the IIF() statements, then take the final resulting number and convert it to a string:

Code: Select all
Str(Year(Ship_date)) + "-" + Str(iif(Month(Ship_date) <= 3, 1, iif(Month(Ship_date) >= 4 AND Month(Ship_date) <= 6, 2, iif(Month(Ship_date) >= 7 AND Month(Ship_date) <= 9, 3, 4))))
Aaron Williams
Kirix Support Team
User avatar
Aaron
Kirix Support Team
 
Posts: 120
Joined: Fri Dec 16, 2005 3:01 pm

Re: iif statements in a calculated field

Postby scbret on Fri Aug 15, 2008 3:06 pm

Thanks Aaron...."logic" is usually my problem (lack of it)
scbret
Registered User
 
Posts: 10
Joined: Tue Jun 24, 2008 9:13 pm

Return to Strata Help & Feedback