Formulas in business logic and pivot tables
Save time your employees spend on data processing by automatically calculating dates and numeric values in section pages and pivot tables. For instance, you can calculate a product price after taxes, an ETA for a task or a contract amendment date. To set up these calculations, use formulas in the “Set field value” business rule and in pivot table settings. You can navigate to the formula setup window:
- by clicking when setting up the Set field value business rule;
- by clicking Add calculated field in the pivot table settings.
This will open the formula setup window (Fig. 1).
All formulas start with “=”. They can have the following elements:
- Constants and variables. For example, you can enter any number or you can use the page field values from the formula setup window's Parameter menu.
- Mathematical operators (+, –, *, /, brackets).
- There also are several function groups used with dates: Date difference, Add to date, Date part, CurrentDateTime. The function by itself is a valid formula, but you can also combine functions with other elements.
You can learn more about the functions and check if they are available for business rules and pivot tables below.
|Function group||Description||Function||Pivot tables||Business Rules|
|Add to date||Adds the specified number of years/weeks/hours, etc. to the date. The calculation result's data type is “Date/Time”.||AddYear||+||+|
|Date difference||Calculates how many years/weeks/hours, etc. there are between the dates. The calculation result's data type is “Integer”.
Creatio uses calendar boundaries to calculate the difference. For instance, DiffYear(2020-12-31, 2021-01-01) = 1
If the first date in the function is later than the second date, the calculation result will be negative.
Determines the number of the original date's year/month/day, etc. The function always uses the 24-hour format. For instance, the date 02.16.2021 3:38 PM contains the following:
|CurrentDateTime||Determines the current date and time. The calculation result's data type is “Date/Time”. The function's brackets have to remain empty.||CurrentDateTime||—||+|
Target field: [Sum to issue] Variables: the values of fields [Per diem] and [Duration, days]. Formula: = [Per diem] * [Duration, days]
Target field: [Duration] Elements: the values of fields [Start date] and [End date]. Formula: = DiffMinute([End date], [Start date])
Target field: [Works for the company, years] Elements: the values of [Career start date] field and the current date. Formula: = DiffYear(CurrentDateTime(), [Career start date])
Target field: [Efficiency, %] Elements: [Cases closed during the month], [Cases processed during the month], [Cases escalated during the month], 100 (percentage conversion multiplier). Formula: = (([Cases closed during the month] + Cases escalated during the month])/ [Cases processed during the month])*100