Expression Tips

Expressions can be hard to read with multiple if() functions. For example:

if(X = A, 1, if(X = B, 2, if(X = C, 3, 4)))

Instead, you can use the case() function:

case(X, A, 1, B, 2, C, 3, 4)

Another example:

```
  case(value("foo") > 5, "good",         
       value("bar") < 2, "ok",      
       "bad")
  ```

 

Expressions can be hard to read with multiple if() functions. For example:

if(A, 1, if(B, 2, if(C, 3, 4)))

Instead, you can use the switch() function:

switch(A, 1, B, 2, C, 3, 4)

More examples:

```
  switch(value("thing"),   
         1, "foo",
         2, "bar",    
         4, "baz",
         "unknown")
  ```

 

calc "Calendar_sort" ```
       switch(calc("Month Name"),
         "January", 1,
         "February", 2,
         "March", 3,
         "April", 4,
         "May", 5,
         "June", 6,
         "July", 7,
         "August", 8,
         "September", 9,
         "October", 10,
         "November", 11,
         12)```

 

calc "_ytd_date" ```
    switch(param("CALENDAR"),
           "Fiscal",   calc("_ytd_date_Fiscal"),
           "Trailing", calc("_ytd_date_Rolling 12"),
                       calc("_ytd_date_Calendar"))```

 

When Boolean types are not available, you can use tricks to set a value to one or zero. Then, test the integer rather than true or false. For example:

if(value("Type") = "x", 1, 0)

Using Spectre Boolean values, this expression becomes simpler and easier to read:

value("Type") = "X"

If using ones and zeros to sum things up, that is, to count what is true,

sum(value("Is_X")),

you can now create an expression that is easier to read as count where something is true:

`count` filter=`value("Is_X")`.

If using ones and zeros to see if there is at least one thing,

sum(value("Is_X")) > 0,

you can use the some() function instead:

some(value("Is_X")).

If a data set has a string value of "Y" to indicate that something is true,

value("FlagStr")="Y",

you can turn it into a Boolean using the type tag:

type="boolean" format="Y;".

Note that the format uses a semicolon to separate the true from the false value. Once the data is set to the Boolean type, expressions downstream are cleaner.

 

Another simplification to avoid lots of parentheses is to use the operators OR and AND rather than the functions or() and and(). For example,

or(A, and(B, C))

is easier to code and read if expressed as:

A or (B and C).

 

A way to avoid multiple OR operators or functions is to use the is_in() function. Rather than using either of the following expressions:

value("X") = "A" or value("X") = "B" or value("X") = "C"

or(value("X") = "A", value("X") = "B", value("X") = "C")

You can use is_in() to perform the exact same action:

is_in(value("X"), "A", "B", "C")