Formulas

Documentation on formulas is available via Functions library. For more complementary information, it possible to see below how to:

Check if an input is null

To check if an input is NULL, it is possible to simply do ( For the moment, function returns cannot be tested this way):

Input1=NULL

For text inputs, it is better to use the ISBLANK function as a text can be NULL or empty for computed text (““).

Check the value of an association

Association n-1 and Workflow

In instances

To check if Impacts status (Association n-1) is At Risk or All good, a filter needs to be set on the input:

The count of instances in Input1 can then be used:

IF(COUNT(Input1)=1, DoSomething, DoSomethingElse)

Is instance

To check if Impacts status (Association n-1) is At Risk, a filter can be set in the input, and then COUNT can be used as above. However, for this particular case, an optimisation can be done: it is possible to define a second input which is the instance At Risk:

And then compare Input1 to this instance ( it only works for association n-1 and workflow when comparing with one instance with the “=” symbol):

IF(Input1=Input2, DoSomething, DoSomethingElse)

Association 1-n and n-n

Contains at least one or several instances

To check if the field Data domain (Association n-n) contains at least the instance Customer & Sales Activation, a filter needs to be set on the input:

It is then possible to use this input like this :

IF(COUNT(Input1)=1, DoSomething, DoSomethingElse)

Contains only given instances

To check if the field Data domain (Association n-n) contains the instance Customer & Sales Activation and Facilities & Sites and no other instances, a filter needs to be set on the input:

And an input without filters also needs to be defined:

It is then possible to use inputs like this:

IF(AND(COUNT(Input1)=2, COUNT(Input2)=2), DoSomething, DoSomethingElse)

Computed association

For the moment, calculated associations are only available for n-n associations. Nevertheless, it is possible to return just one instance. However, in paths, this will always be considered as n, regardless of what is returned.

An example of this can be:

• Yes is returned if Time in Delivery > Average Time in Delivery.

• No is returned if Time in Delivery <= Average Time in Delivery.

Re-use filters on instances

For reasons of performance and practicality, it may be useful to create a calculated association n-n representing a subset of given instances.

Let's imagine, for example, that the average of different indicators for a given set of instances needs to be calculated. The classic way will be to have for each indicator:

• As Input : Use case > All instances (filters) > Indicator X.

• And then do:

AVERAGE(Input)

In this case, it can be interesting to create a computed n-n association representing the subset of instances using the GETOBJECTS function:

And then use this computed association n-n for the computed number representing the average of the indicator:

It is interesting in two ways:

• Performances: If several indicators are represented on a Dashboard, for example, the filter will only be calculated once, which will result in a significant gain in performance if the number of instances to be filtered is large.

• Practicality: If changes need to be made to the filter, they will only need to be made in one place.

Dates

Add time to a date

In this example, a computed date needs to be defined by adding Estimated Time Frame (a number, which represents a number of weeks) to a date (Predicted Starting date).

To do so, the number of weeks needs to be transformed into the number of milliseconds representing those weeks, and this number of milliseconds needs to be added to the starting date. Furthermore, when the Predicated Starting date or the Estimated Time Frame is NULL, the computed date is set to NULL:

IF(OR(Input2=NULL, Input1=NULL), NULL, DATECONVERT(MAX(Input2) + 
                                                   Input1*7*24*3600*1000), "DAY")

The explanation of the formula above is the following:

Input1 (number of weeks) * 7 (number of days in a week) * 24 (number of hours in a day)
 * 3600 (number of seconds in an hour) * 1000 (to make the seconds into milliseconds).

Get today’s date

To get the today’s date, it is possible to do:

DATECONVERT(NOW(), "DAY")

Check if an instance has been created this week

To check if an instance has been created this week, the formula below can be used:

IF(DATECONVERT(Input1, "DAY") + WEEKDAY(NOW(), 2)*1000*3600*24 > NOW(), TRUE, FALSE)

By Input1 being the Created at date.

The formula will return TRUE for any instance created since Monday.

Timeseries

Cumulative timeseries

It is possible to get a cumulative timeseries from a timeseries input in a very simple way:

It is important to note that the input timeseries should be a unique timeseries and not a table of timeseries (it is not possible to sum and cumulate timeseries at the same time). If it is needed to do so, it can be done in two steps:

• Create a computed timeseries of the sum (see Sum timeseries ).

• Create a computed cumulative timeseries from the timeseries of the sum, as shown above.

Sum timeseries

As a timeseries

It is possible to create a computed timeseries representing the sum of some timeseries doing this:

The resulting timeseries will have for dates all the dates from the timeseries of the input.

For example, if there are three Use case that have for timeseries (periodicity yearly) the ones below:

Use case 1 timeseries:

Use case 2 timeseries:

Use case 3 timeseries:

The computed timeseries (periodicity yearly) will be:

As a number

It is possible to have a number representing the sum of all (or some) of the values of a given timeseries, using the VALUESIN function :

SUM(VALUESIN(Input))

It is important to note that VALUESIN only accept a single timeseries, and not an array of timeseries. Therefore, Input should have a cardinality 1.

To sum multiple timeseries for all dates, it must be done in two steps:

1. Create a computed timeseries of the sum.

2. Create a computed number using the computed timeseries as input.

Compare timeseries

It is possible to compare timeseries. There are two ways of doing it that can lead to a different result. There is no better way, it depends on what needs to be achieved.

To illustrate, an example will be taken in which a comparison will be made between the values of a timeseries given a reference timeseries.

The reference timeseries is the following:

The timeseries that is compared to the reference timeseries is the following:

Using one timeseries at time reference

With this computing, the result will be the following:

Only the dates of the Input1 are used for the computed timeseries. We are able to retrieve the values of the reference timeseries by using the VALUEAT function and the parameter ExecutionInstant_i.

Using both timeseries as time reference

With this computing, the result will be the following:

All the dates found both in Input1 and Input2 are used for the computed timeseries.

Boolean field computed

For boolean field computed, TRUE, FALSE or NULL (will be considered as FALSE) needs to be returned by the formula. It can be done in several ways:

• Using TRUE, FALSE or NULL directly.

• Making a formula that generate a boolean (TRUE or FALSE) or NULL. For example : Input1 > 50 generates a TRUE or FALSE based on the value of Input1.

As a reminder, booleans can be used in formulas as input, these inputs return TRUE or FALSE.