Calculation

Location: Site Configuration > Module Configuration > Field Definition Configuration

Setting Field Definition of Calculation content allows performing various types of calculations in the system.

The calculation field can be of 3 types: Decimal, Date, Text. Depending on the type it is possible to use formula that produces the result compatible with the type chosen.

• Decimal Number — produces a numeric decimal value;

• Date — produces the result of type Date formatted by mask, e.g. dd-MM-yyyy;

• Text — produces the result of both decimal and date as a text, that can be concatenated with a custom text that allows to display more human readable data.

  Update setting is responsible for updating calculation fields in Edit window:

• On the Fly — Calculation field will be updated immediately as one of its constituents is update

• When Saving — Calculation field will be updated after Edit window is closed

When Show Calculation in “Totals” checkbox is selected for a calculation fields, the calculation in total row will be performed using already calculated totals of the formula constituents.

If Calculation is selected for Field Content, this window will let enter the formula according to which calculation of the field value will be performed. With the help of formula constructor that is available under the Formula text area, it is possible to compose calculation formula using formula syntax, fields placeholders, arithmetic operators and numeric constants. e.g. (${Integer}+${Decimal numeral})/2

Sum/SumIf calculations

To perform calculation across modules it is necessary to have Module Link or referencing to this Module Link Field.

For direct link Calculation (Module Link from this Module), formula will have next form:

${fieldFromThisModule}+sum(${modulelinkFieldFromThisModule}, 
${LinkedModule:fieldNameInLinkedModule})

For reverse link Calculation (Module Link to this Module), formula will have next form:

${fieldFromThisModule}+sum( 
${moduleWithLinktoThisModule:FieldNameOfModuleWithLinktoThisModule}, 
${moduleWithLinktoThisModule:fieldNameFromModuleWithLinktoThisModule})

For the second scheme sum of values from the Module with Module link field will be used to perform calculation.

It is also possible to perform calculation across modules with applying filters. For example we need to calculate bill for all products with ‘Shipped’ status for each of orders. Next configuration is possible: 2 Modules Orders and Products are available. Module Products contains next fields:

• Status — Status field with statuses available for items;

• SalePrice — Decimal Numeral field with Sale Price of the item;

• Name — Text, Name field;

• Orders — Multi-select Module link to module Orders

The next fields will be available for Orders module:

• Name — Text, Name field;

• Total for Shipped items — Calculation field with next formula:

sumif( (${Products:Status} == ${Products:Status:Shipped}), ${Products:Orders}, ${Products:SalePrice})

Where:

• (${Products:Status} == ${Products:Status:Shipped}) — filter set up to perform calculation for Products with ‘Shipped’ Status

• ${Products:Orders} — Module link field that references to module with calculation field

• ${Products:SalePrice} — Field that will be summed for items by applying filter

There are some basic rules you must follow to set up calculations with filtering.

1. SUMIF syntax differs depending on the field type of the field used as filter

2. When filtering is to be set up by single select field (simple option or module link) syntax will have next view:

   sumif((${Products:Status} == ${Products:Status:Shipped}), ${Products:Orders}, ${Products:SalePrice})

3. In case ‘Status’ is a field of Multi-select type

   sumif((contains(${Products:Status},${Products:Status:Shipped})), ${Products:Orders}, ${Products:SalePrice})

   Please Note: for multi-select contains operator is used, its constituent must be placed inside brackets.

4. When filtering field (Status in our example) is module link field it is possible to use the next syntax as well:

   sumif((${Products:Status} == 'Shipped'), ${Products:Orders}, ${Products:SalePrice})

5. Filtering can be set up to use multiple criteria by using && (AND) || (OR) logical operators. Next example will sum field items that have ‘Shipped’ or ‘On Ship’ status

   sumif((${Products:Status} == ${Products:Status:Shipped} || ${Products:Status} == 
   ${Products:Status:On Ship}), ${Products:Orders}, ${Products:SalePrice})

6. In all of the examples above — “reverse” calculation is used (when calculation is performed in module that contains module link to the current module).

   sumif( (${Products:Status} == ${Products:Status:Shipped}), ${Products:Orders}, ${Products:SalePrice})

   But in case Orders module contains ProductsInOrder — module link of multi-select type to Products module. In such a way there is no need to specify module name for linking field:

   sumif( (${Products:Status} == ${Products:Status:Shipped}), ${ProductsInOrder}, ${Products:SalePrice})

7. Only next logical operators can be used:

Conditional calculations

There is a possibility to use conditional filters in calculation fields without getting a summarizing value. This functionality works similar to SUMIF, but it has some restrictions. Common syntax is:

(expression ? formula 1 : formula 2)

where

expression — logical condition in which two or more operands are compared. Such logical operators can be used: && (AND), || (OR), == (equals), != (not equals), > (more than), < (less than), >= (equals or more), <= (equals or less)

formula 1 — will be chosen for calculation if expression is TRUE

formula 2 — will be chosen if expression is FALSE

For example, we want to calculate the expected turnover by multiplying the number of sold products on their campaign price. And if campaign price is empty or equal to zero, normal price value should be used in calculation. It can be done in such way:

(${campaign_price} != 0 ? ${number_items} * ${campaign_price} : ${number_items} * ${normal_price})

Functions for working with dates:

Examples

Current limitations

• Not possible to use calculation (date) as filtering criteria (widgets, export, reporting);

• Not possible to combine date calculations and SUMIF in one formula, use separate fields for this.

Duration VS DateDif

Starting from version 9.106.0 we distinguish 2 types of date calculations returning the interval between the 2 days: dateDif and duration.

dateDif

dateDif(${Start date}, ${End date}, "days")

Calculation field returning the number of days between 2 date fields, excluding start date.

Example:
Project start date: 01/01/2021
Project end date: 02/01/2021
Date difference will be equal to 1.

In another example:
Project start date: 01/01/2021
Project end date: 01/01/2021
Date difference will be equal to 0.

Duration

durationDays(, , , )

Date calculation field that will return the duration value based on the configuration options allowing to include/exclude start/end date in duration calculation.

Examples:

Formula: durationDays(${Start date}, ${End date}, true, true)
Project start date: 01/01/2021
Project end date: 02/01/2021
Duration will be equal to 2.

Formula: durationDays(${Start date}, ${End date}, true, false)
Project start date: 01/01/2021
Project end date: 02/01/2021
Duration will be equal to 1.

Formula: durationDays(${Start date}, ${End date}, false, true)
Project start date: 01/01/2021
Project end date: 02/01/2021
Duration will be equal to 1.

 Formula:durationDays(${Start date}, ${End date}, false, false) Project start date: 01/01/2021
Project end date: 02/01/2021 Duration will be equal to 0.

Special characters in formulas

Special characters in formula are handled fine except of two cases:

1. Field Definition or field option (in case filtering is performed by multi-select field) contains curly brackets { or } — they should be simply escaped by enclosing in single quotes. For example: let’s rename Status field to — Status {shipping}

   sumif( (${Products:Status '{'shipping'}'} == ${Products:Status '{'shipping'}':Shipped}), ${Products:Orders}, ${Products:SalePrice} )

2. In SUMIF formula Status is module link and we need to filter by its value that contains single quote '. Single quote is escaped in formula with double backslash \\. E.g. filtering by Courier’s deliver option:

   sumif( (${Products:Status} == 'Courier\\'s deliver'), ${Products:Orders}, ${Products:SalePrice})

3. In conditional calculation formula the option of Status field contain single quote. It should be escaped by double backslash \\ (single backslash \ also can be used):

   ${Status} == 'Courier\\'s deliver' ? ${price1} : ${price2}

Optimized calculation for sum(), min(), max() and count()

Available in version 17.0.0

The target for optimization – aggregation operations on items from another module, linked to the current item. The formula should have a “valid“ pattern to be optimized.

Optimized aggregation operations support correct calculation when the value field is a decimal/integer multi-select (or checkboxes, or any other multivalue field type).

Optimized: Calculation (Decimal Number), formula:

sum(${Estimate Line items:Brief Name}, ${Estimate Line items:Total (Brief Currency)}

count(${Products:Campaign})

Not optimized: Calculation (Decimal Number), formula:

 0+sum(${Job Import Validation:Brief ID}, ${Job Import Validation:Count}) 

count() 

How to determine if the new optimized calculation has been applied to the field calculation?

1. Navigate to System Configuration > System Status > System Change Log

2. Search for logs where Field definition for Calculation was created or updated

3. Search for 'Database Calculation:' in the 'Details' column and check that the boolean value is 'true'. In the case 'false' is displayed, the calculation for the field is not optimized. 

Important! Changes to how multi-value fields are calculated:

The current (not optimized), calculation prior to v.17, takes only the first selected option from the multi-value field and does aggregation on first values only:

For example: 9 items from 'Briefs' module with a multi-value field are linked to 3 items in 'Campaigns' module

In the 'Campaigns' module the calculation field displays the sum() of the first value of each 'Briefs' item 5+5+5 = 15, 4+4+4 = 12 and 3+3+3 =9:

New 'optimized' calculation takes into consideration all values of each 'Brief' item from the multi-value fields:

Resulting in different total displayed for each item in 'Campaigns' module:

e.g. (5+2+1)+(5+2+1)+(5+2+1) =24 | (4+3)+(4+3)+(4+3)=18 | (3+2+1)+(3+2+1)+(3+2+1)=18

Known Behaviour:

• Does not optimize sum() if the fields are in the same module

• Only optimized for field definition type: Calculation (Decimal Number) - Calculation (Date) or Calculation (text) are not supported

• Currently, only standalone calculations are going to be optimized:
  e.g. sum(${Products:LinkToCampaign}, ${Products:Quantity}) is optimized, but sum(${Products:LinkToCampaign}, ${Products:Quantity}) + 12 nor sum(${Products:LinkToCampaign}, ${Products:Quantity}) + sum(${Products:LinkToCampaign}, ${Products:Quantity}) will not be optimized

• Currently for count(), optimization covers only the formula that counts linked items:

  In the module Campaigns create a formula: count(${Products:Campaign})This formula shows the amount of Products linked to the current Campaign, by a module link “Campaign“ in the “Products“ items.