Creating and Configuring Formulas
Last updated on 2024-11-20
Overview
Controlling indicators such as the absolute liquidity ratio, the equity ratio, and the return on sales can be created in Lucanet using formulas. Formulas can be used in all ledgers and schedules to calculate any values. A formula comprises variables and mathematical operators. Each variable of a formula can apply to an item, an account, a different formula, a time series, or a total line in the Lucanet database.
This article contains the following sections:
Formula element
Description and procedure
Variables
The variables of a formula can be an item, an account, a different formula, a time series, or a total line. Variables can be named arbitrarily.
Strings put in inverted commas (") are not regarded as variables.
Examples:
Final
A
Equity
Operators
The following operators can be used in a formula:
= > < <> >= <= ( ) AND OR IF + - / * LM LAST_MONTH
How to use IF, LM and LASTMONTH is described in detail in Special formulas.
Examples:
a = b
a > b AND b < d
Creating and Configuring a Formula
How to create and configure a formula is described in the following section. Alternatively, you can find the information in the following video:
Creating Formulas
To create a formula:
- Open the ledger (general ledger, subledger, or statistical ledger) and navigate to the item where you want to create the formula.
- Right-click the item and choose Create | Formula from the context menu.
Creating a formula
Configuring Formulas
The following options are available in the Create formula dialog:
Dialog 'Create formular' - 'General' tab
Description
Type
Element type
Name
Name of the formula
Bold format
Formats the title of the formula in bold
Formula
Variables, operators, and, if necessary, values of the formula. The entered variables are transferred to the following table.
The table contains the configuration of the variables:
| Element | Description |
|---|---|
| Variable | All variables entered in the Formula field are automatically transferred to this column. |
| Elements | Elements that represent the variables. Possible elements are items, accounts, time series, total lines, and formulas. |
| Transaction type |
Transaction type to be used for the calculation |
| Period type | The period type of the chosen account or chosen item is preset by default. The following options are available:
|
| Year-to-date | Year-to-date value from that the calculation is to be made up to the current period. Otherwise the value will be calculated for the current period. |
is activated, the transaction figure of the period is used.
Dialog 'Create formula' - 'Value type' tab
Description
Value type
Specification of the value type for the output value of the formula. The following options are available:
- Number: The output value is displayed as a number.
- Percent values: The output value is displayed as a percentage value.
- Checksum: The output value is displayed as a checksum.
The checksum in the event of a successful check must be 0. In this case the output value is displayed in green. Otherwise, the value is displayed in red:
Display of checksums
Decimal places
Specification of the decimal places for the output value of the formula. The following options are available:- Dependent on variable: The display of the decimal places is taken over by the variable.
Note: This value is available only for the Number and Checksum value types. - User-defined: Enter the number of decimal places in the displayed input field.
Dialog 'Create formula' - 'Number format' tab
The Number format tab is only displayed for formulas in statistical ledgers.
Description
On the Number format tab, the way numbers are displayed for a formula can be configured. The following options are available:
Apply number format
Applies the number format that is different from the display settings
Number display
Formatting of the displayed values
Decimal places
Number of displayed decimal places
Configurations on the Number format tab overwrite the number format defined in the display settings.
As soon as a deviating number format has been configured, the icon 
is displayed in the row of the formula.
Special Formulas
LM and LAST MONTH
Example 1
Calculation of the revenue of the last six months, with the variable a standing for the revenue item. In this case, the formula is as follows:
LM(6;a)
IF formula
An IF formula can be used, for example, to calculate interest if a different interest rate is to be applied to positive than to negative balances on the account. The general structure of an IF formula is as follows:
IF(condition;then value;else value)Example 1
In the following formula, b is output if a = 10 applies. C is output in the event of any other value:
IF (a=10;b;c)
Example 2
In the following formula, b is output if a is smaller than 10 or a is greater than d. C is output if both conditions do not apply:
IF (a<10 OR a>d;b;c)
Example 3
The following video shows the development of a formula for the interest: