Calculations – hubcloud.io

Use Calculations to create and validate calculations that solve business tasks.

For example, when we are entering the list of purchased products after entering the quantity and price, it is desirable that the amount is calculated automatically.

Similar tasks can be performed using formulas. A formula can be written for any field Header or rows in the Table. After calculating the formula, the resulting value will be assigned in the field that contains the formula.

To Calculate data in the operation field, enter the formula which performs the necessary calculations. ( see Fig.1 )

Formula Execution Context

It is important to understand the context of the execution of formulas. In the formulas, the values in the Header fields are available, the values in the current row of the Table, as well as the results for the numeric fields of the Table:

$h.name – Header field (” h ” means header);
$r.name – Table field of the current row of the Table (“ r ” means row);
$t.name is the total value calculated from the Table field ( ” t ” means total);

Catalog’s Items are available to use in formulas. Example:

$h.budget_item == Catalog.BudgetItem.Sales ? 2500 : 0

Formula Syntax

In most cases, the formulas in operations are quite simple since limited to understandable arithmetic operations and their writing does not cause difficulties. In this chapter, we give only the minimally necessary operators.

Arithmetic operators

Name	Operator	Examples
Addition	+	*$r.amount+$r.tax*
Subtraction	–	*$r.total – $r.tax*
Multiplication	*	**$r.quantity $r.price***
Division	/	*$r.amount / $r.quantity*

Comparison operators

Name	Operator	Examples
Equally	==	$r.amount1==*$r.amoun*t2
Not equal	!=	*$h.store != $r.store*
Is more	>	$h.total > 1000
More or equal	>=	$h.amount >= 1000
Is less	<	$h.total < 1000
Less or equal	<=	$h.amount <= 1000

Logical operators

Name	Operator	Examples
Logical “And”	&&	$h.total > 1000 && $h.amount >= 1000
Logical “OR”	\|\|	$h.total > 1000 \|\| $h.amount >= 1000
Logical “NOT”	!	*$h.store != $r.store*

Conditional operators

Ternary operator

The ternary operator allows you to calculate values in formulas depending on the condition. The syntax of the ternary operator is as follows:

(Condition) ? ExpressionYes : ExpressionNo

Condition – Comparison operators expression;
ExpressionYes – Result or Expression If Condition is True
ExpressionNo – Result or Expression If Condition is False

Example: calculate discount – ( $h.total > 1000 ) ? $h.total * 0.1 : h.total * 0.05

Ternary operators can be nested within each other, for example:

(Condition1) ? ((Condition2) ? Expression-2-Yes : Expression-2-No) : Expression-1-No

if – operator

It can be used instead of a ternary operator.

if (Condition, ExpressionYes, ExpressionNo)

Condition – Comparison operators expression;
ExpressionYes – Result or Expression If Condition is True
ExpressionNo – Result or Expression If Condition is False

Example: calculate discount – if ( $h.total > 1000 , $h.total * 0.1, h.total * 0.05 )

ifs – operator

It is simulating an “IF – ELSE” logic. Computes sequentially an arbitrary number of logical expressions. Returns the first value for which the expression is true. If all evaluated expressions are false, null is returned.

ifs ( Condition1, Result1, [Condition2, Result2, …])

Condition – Comparison operators expression;
Expression – Result or Expression If Condition is True

Example: calculate discount – ifs ( $h.total > 1000 , $h.total * 0.1, $h.total > 2000, h.total * 0.15, $h.total > 3000, h.total * 0.3, $h.total < 1000, 0 )

switch – operator

Computes the expression and returns the first value corresponding to the result of the calculation.

switch ( Expression, Result1, Value1, [Result2, Value2, …])

Expression – an arbitrary expression or value.
Result1, …, ResultN – values compared with the result of evaluating the expression.
Value1, …, ValueN – the values returned if the calculation result matches.

If the result of evaluating an expression is equal to result1, then the value1 will be returned, if the result of evaluating an expression is equal to result2, then the value2 will be returned, and so on. If no match is found, null will be returned.

Example: calculate rate – switch ( $h.amount, 1 , $h.rate1, 2, $h.rate2, 3, $h.rate3 )

Date Calculations

Date difference

Calculates the number of days between the specified dates.

date_difference( dateStart, dateFinish, periodType)

dateStart – start date
dateFinish – end date
periodType – type of period for which the difference is calculated. The type of period can take values: second, minute, hour, day, month, year
The return value is a number.

Example: calculate number of days between stat and end dates – date_difference( $r.start, $r.end, “day”)

Add Day

Calculates the date from the given to the given interval days.

add_day( $r.start , days )

$r.start – start date
days – interval days
The return value is data.

Example: calculate date from $r.start to the next week – add_day( $r.start , 7 )

Calculation of standard dates

standard_date( date, standardDateName)

date – date for which calculations are performed.
standardDateName – string, name of the standard date.

Available standard date values is:

BeginDay
EndDay
BeginNextDay
EndNextDay
BeginLastDay
EndLastDay
BeginWeek
EndWeek
BeginNextWeek
EndNextWeek
BeginLastWeek
EndLastWeek
BeginMonth
EndMonth
BeginNextMonth
EndNextMonth
BeginLastMonth
EndLastMonth
BeginQuarter
EndQuarter
BeginNextQuarter
EndNextQuarter
BeginLastQuarter
EndLastQuarter
BeginYear
EndYear
BeginNextYear
EndNextYear
BeginLastYear
EndLastYear

Calculation a Date by a Catalog Item

It is assumed that there is a Catalog to periods in a row. January, February, March, etc. It is important that item identifiers go in a row. Then the date for the element can be obtained by the formula:

month_from_catalog( $h.date , $h.month , idStart)

$h.date – any date of the year, it is convenient to use the date of operation.
$h.month– field type Catalog.Periods.
idStart – element identifier January. The identifier can be viewed in the source console.

Constructor of Model