Joining Calculated Fields

[Aramark]

Hi,____I^m setting up a Fiscal periods based on arrival dates, and with the size limitations in a calculated field, i can only set up periods 1-5 in one calulated field (FISCAL1), and periods 6-10 (FISCAL2) and periods 11-12 in (FISCAL3). How can i join (concatenate) these 3 fields (so i can query on an entire year)..??____thanks

[kfleming]

What is the expression of each of your fields? You can create a calc that references another calc that can help with lengthy or complex expressions. ____Kathleen__R&R Support__

[Aramark]

IIF(ARRIVAL>={05/01/04} AND ARRIVAL<={05/28/04},8,IIF... and so on. Please explain the calc reference... i tried to do something like that.____thank you very much!

[kfleming]

Expression for CALC1 will be__IIF(ARRIVAL>={05/01/04} AND ARRIVAL<={05/28/04},8,IIF... ____So this guy checks dates and returns numbers.____Then we create CALC2 whose expression is:__IIF(ARRIVAL>={07/01/04} AND ARRIVAL<={07/28/04},12,IIF(ARRIVAL>={08/01/04} AND ARRIVAL<={08/28/04},13,CALC1))____So CALC2 checks some stuff and his default value if none of them are true is to check CALC1.____We could then create CALC3 to do more stuff and have his final default be CALC2.____Then when we use CALC3 he chains back to CALC2 who goes back to CALC1.____Kathleen__R&R Support

[Aramark]

i did all that, created 3 calc fields... but still have problems:____1. In calc3, else is calc2 and in calc2, else is calc1, what is the "else" clause in calc1??__2. and which calc field do i use for the Group Footer so that i can break on fiscal periods..?____thanks for your help...

[kfleming]

The else in CALC1 is just a number.__So calc1 might return 1, 2 or 3__Calc2 returns 4,5,6 or CALC1__Calc3 returns 7,8 or CALC2____So if you use CALC3 as your group field it will give you the break for 1,2,3,4,5,6,7,8____Kathleen__R&R Support