DECLARE function (version 2.6+)

The DECLARE function allows you to declare a value using an expression (mini formula), then use that value repeatedly within the rest of your formula. 

It is highly useful in complex formulae where you find yourself repeating the same sub-formula many times, as it makes your formula much clearer, and can yield better performance.

Simple example using a single value declaration

In its simplest form, DECLARE allows you to declare one named value as follows:

DECLARE(

     myValue, 1+2+3,

     myValue*myValue + myValue

)  

(Result: 6)

The first two parameters are equivalent to saying "Let myValue = 1+2+3" and declare the value "myValue" as the result of "1+2+3" (i.e. 6).  Here myValue is a made-up name which can be whatever you want providing you do not use spaces or numeric operator characters, and no field or variable already exists with the same name.

The last parameter is the formula to evaluate in the knowledge that "myValue" is equal to "6".

This is the same as the pseudo-code:

LET myValue = 1 + 2 + 3

RESULT = myValue * myValue * myValue

This would be the same as the more repetitive formula:

(1+2+3)*(1+2+3) + (1+2+3)

Advanced example using multiple value declarations

Multiple values can be declared by providing more pairs of [name, value] arguments at the start, as seen in this example, which takes a date field (Maturity Date) and breaks it into the numeric Month and Year values, before converting into a long textual representation of that date.

DECLARE(

    month, DATEUNIT(Maturity Date, "month"),

    year, DATEUNIT(Maturity Date, "year"),

    IF(
       month<=3, "1st quarter",
       month<=6, "2nd quarter",
        month<=9, "3rd quarter",
        "4th quarter"
    )
    + " of year " + MOD(year, 100) + ", " +
    IF(
        year < 1900, "before last century",
        year < 2000, "last century",
        year < 2100, "next century",
        "after next century"
    )

)

(Example result: "2nd quarter of year 7, next century")

This is the same as the pseudo-code:

LET month = [month number for Maturity Date]

LET year = [year number for Maturity Date

RESULT = [various calculations using month and year]

This would be the same as the more repetitive formula, which is more error-prone and less clear:

IF(
    DATEUNIT(Maturity Date, "month")<=3, "1st quarter",
    DATEUNIT(Maturity Date, "month")<=6, "2nd quarter",
    DATEUNIT(Maturity Date, "month")<=9, "3rd quarter",
    "4th quarter"
)
+ " of year " + MOD(year, 100) + ", "
IF(
    DATEUNIT(Maturity Date, "year") < 1900, "before last century",
    DATEUNIT(Maturity Date, "year") < 2000, "last century",
    DATEUNIT(Maturity Date, "year") < 2100, "next century",
    "after next century"
)