Example: Formula

Applying Formulas and SQL Functions to Characteristics

In the example, we apply a simple SQL formula to compare two fields containing dates and we mark a third field according to that formula.

Input Data

Contract ID Date_1 Date_2 Total Premium
A 2019-01-01 2019-02-01 300
B 2019-04-01 2019-03-01 400

Flow Modeling Configuration

Rules (Tab)
Rule Description Rule Type State Rule Grouping Rule Ordering Selection
CHARF Characteristic Formula Characteristic Formula Active      
Rule Line
Line Formula Result
L001
CASE
WHEN DATE_1 > DATE 2
THEN 'X'
ELSE "
END
DATE_IND

When DATE_1 is greater than DATE_2, the field DATE_IND (Date Indicator) is marked with “X”.

Key Configuration Description

The table below explains the meaning of the options required to run this configuration.

Field Meaning Notes
Line Identifier of the line Must be unique. A single characteristic formula rule can run multiple lines.
Formula SQL formula that has to be applied to categorical variables Mandatory input
Result Field where the result of the rule is stored

Mandatory output.

Must be defined in the Action section of the Signature tag.

Expected Result

Contract ID Date_1 Date_2 Total Premium Date Indicator
A 2019-01-01 2019-02-01 300  
B 2019-04-01 2019-03-01 400 X

Applying Formulas and SQL Functions to Key Figures

In this example, we apply a very simple SQL formula to compute the weighted premium (WEIGHTED_PREMIUM) based on the fields TOTAL_PREMIUM and WEIGHT.

Input Data

Contract ID Date_1 Total Premium Weight
A 2019-01-01 300 0.4
B 2019-04-01 400 0.6

Flow Modeling Configuration

Rules (Tab)
Rule Description Rule Type State Rule Grouping Rule Ordering Selection
KFF Key Figure Formula Formula Active      
Rule Line
Line Formula Result
L001 TOTAL_PREMIUM*WEIGHT WEIGHTED_PREMIUM

Key Configuration Description

The table below explains the meaning of the options required to run this configuration.

Field Meaning Notes
Line Identifier of the line Must be unique. A single Key Figure Formula rule can run multiple lines.
Formula SQL formula that has to be applied to numerical variable Mandatory input
Result Field where the result of the rule is stored Mandatory output. Must be defined in the Action section of the Signature tag.

Expected Output

Contract ID Date_1 Total Premium Weight Weighted Premium
A 2019-01-01 300 0.4 120
B 2019-04-01 400 0.6 240