Expression syntax
Expression syntax
When we are working in setting up static diagrams as well as certain places in dynamic diagrams and in report management pages, we need to understand the syntax for expressions. The help files on CGScript explain these to some extent, but the following document is focused more clearly on using expressions in a report module context.
Let us first observe an example of an expression sheet with some expressions.
In the above we are presented to two different types of expressions:
- Functions used in where expressions
- Math functions
Math functions are functions that use data of other cells to calculate a result whereas 'where' functions calculate results based on data picked up from columns of a data cache.
The symbol '=' first in a cell indicates that a cell is used to calculate expressions. This is known as an assignment operator. It assigns the cell to be calculated. As we will see later, this assignment operator differs from the logical operator called '==' used in 'where' functions.
Notice that we can also use expressions in inline expressions. An example of this is shown below:
When we create expressions inside the inline expression editor we do not need the assignment operator '=' since the system already knows we are about to make an expression. Therefore you can just state the function to use immediately. You have the choice when listing out the answers to see them in one long string or to take each users' answers in separate rows. What is smartest to do will very much depend on the type of answers you are expecting. To make it easy to identify when one users answer ends and another ones starts you can separate them by the separators offered in this drop down.
Math functions
We currently offer 4 types of Math functions; sum, average, min, max. They all need to be written in the format '=f(a:b)' where f identifies the math function, and a and b the first and last cell in a row or column of cells.
Examples of valid math expression used in a spread sheet would be:
= sum(C2:C3)
= average (A1:D1)
= max (A1: A100)
= min (B2:Z2)
'Where' functions
The where function looks like follows
= f(x) where (data_cache_column operator [value]) && (data_cache_column2 operator2 [value2])
Notice that the symbols surrounding the expression in the examples further above { and } are not required and actually illegal from version 5.5.
Let us first examine which functions we have (f). You can read details on the functions by clicking on them:
functions returning a numerical value
functions returning arrays
Notice that the functions returning arrays are mostly useful for inline expressions. Some of the above functions require you to place a value in the parenthesis after the function, some do not. But all of them do require that you specify a condition after the where statement. Conditions in a where statement should be surrounded by parentheses and with 'and' or 'or' operators in between them. The and operator is written as '&&' and the or operator as '||'.
In cases where you do not wish to put a condition on a function you can just write true instead, e.g.
average(score) where true;
This expression will return the average for the data cache column called score.
Now let us try to only get results where the gender is male (male is defined as option 1 for the gender column).
average(score) where (gender==1);
Here you see that we have added a condition so that we only get the average for all rows in the data cache that satisfy the conditions. Now let us add some additional conditions.
average(score) where (gender==1) && (age>=16) && (country==[1-45, 66]);
In the above expression you have seen us use a number of different logical operators - to learn the full list of possible logical operators please click here!
Finally you also notice that it is possible to specify ranges of numbers inside the [] symbols. If we only compare with one number e.g. abc==1 then we do not need the symbols (although they also do not make any problems if applied). But if we compare a column with a range the need to be used. Examples of ranges can be found by clicking here!
Back to: Expression
