Modelling

Modelling is used to create scenario-specific rules from list records. A modelling rule calculates amounts for a selected GL account by using values from a selected list, period variables, and other rules configured for the same scenario and list.

Access and permissions

Modelling is available from the xpna Web navigation when the feature is enabled for the workspace.

• Workspace administrator access is required.
• A scenario and list must be selected before rules can be configured.
• Rules are saved against the selected scenario and list.
• List records should be reviewed before rules are added or updated.

Note:

Changes to rules affect the selected scenario and list. Rule changes should be reviewed with Run Preview before the rule is saved.

Page layout

The Modelling page contains the following areas:

• Scenario: Selects the scenario whose modelling rules should be managed.
• List: Selects list records that exist in the selected scenario and provide the modelling context.
• Rules: Shows configured rules for the selected scenario and list.
• Rule editor: Used to add or update a rule name, GL account, and expression.
• Expression tools: Shows period variables, functions, and rule variables that can be inserted into an expression.
• List records: Shows the records available to the expression.
• Rule Results: Shows preview results for the current draft rule.

Creating a rule

A new rule can be created from the Rules panel.

• New is selected to start a draft rule.
• A rule name is entered.
• A GL Account is selected from the account member picker.
• An expression is entered using list columns, period variables, rule variables, and supported functions.
• Run Preview is used to review the calculated results.
• Add Rule is used to save the rule.

After a rule is saved, it is listed in the Rules panel for the selected scenario and list.

Updating a rule

An existing rule can be selected from the Rules panel and edited.

• The rule name, GL Account, or expression can be changed.
• Run Preview can be used before the updated rule is saved.
• Update Rule saves the rule and refreshes the configured results for the selected scenario and list.
• Cancel discards the current draft changes and clears the editor.

Building expressions

Expressions are calculated for each record in the selected list and across the scenario periods.

The following values can be used in expressions:

• List columns shown in the records table, referenced as [@Column Name].
• Period variables:
  • PeriodStart: The start date of the current period.
  • PeriodEnd: The end date of the current period.
• Rule variables from other configured rules in the same scenario and list.
• Supported functions shown by the expression editor, including Coalesce, DateAdd, DateDiff, DatePart, IsBlank, Lookup, and MonthFraction.

Coalesce can be used to return the first value that is not blank:

• Coalesce([@EndDate], PeriodEnd) returns EndDate when it has a value, otherwise the current period end.

DateAdd can be used to add a fixed number of date parts to a date:

• DateAdd("month", 1, [@StartDate]) returns the date one month after StartDate.
• Date parts are provided as quoted text, such as "day", "week", "month", or "year".

DateDiff can be used to return the number of date-part boundaries between two dates:

• DateDiff("day", [@StartDate], PeriodStart) returns the number of days from StartDate to the current period start.
• DateDiff("month", [@StartDate], PeriodStart) returns the number of month boundaries between the two dates.
• A positive value is returned when the end date is after the start date.

DatePart can be used to return one part of a date:

• DatePart("month", PeriodStart) returns the calendar month number of the current period start, from 1 to 12.
• DatePart("day", [@StartDate]) returns the day of month from StartDate.

IsBlank can be used in conditional expressions:

• If(IsBlank([@EndDate]), 1, 0) returns 1 when EndDate is blank, otherwise 0.

MonthFraction can be used to return the fraction of the current period that a list item is active:

• MonthFraction([@StartDate], [@EndDate]) returns the overlap between the start and end dates and the current period.
• A blank start date is treated as the start of the current period.
• A blank end date is treated as the end of the current period.
• For example, [@Salary] * MonthFraction([@StartDate], [@EndDate]) can be used to prorate salary when an employee starts or ends part-way through a period.

Lookup can be used to return a value from another list dataset in the same scenario:

• Lookup("Dataset name", "MatchColumn", [@Match Value], "ReturnColumn") returns the first value from ReturnColumn where MatchColumn matches [@Match Value].
• Additional match pairs can be supplied before the return column, for example Lookup("Rates", "Region", [@Region], "Role", [@Role], "Rate").
• Dataset names and column names are provided as quoted text.

Round can be used to round a value to a fixed number of decimal places, for example Round([@Amount], 2).

Column headers in the list records table can be selected to insert the corresponding expression variable. Mapped dimension columns can be compared with member name text, for example [@Region] = "NSW".

Note:

Scenario is selected outside the expression. ScenarioKey is not available as an expression variable.

Previewing rule results

Run Preview calculates the current draft rule without saving it.

• Preview results are limited to a sample of matching rows for review.
• The Name column identifies the list record used for the calculation.
• Custom dimensions from the list records are carried into the results and displayed as member chips where available.
• Period Start, Period End, and Amount show the calculated transaction result.
• Preview SQL can be copied when troubleshooting or support review is required.

Saving rule results

When a rule is added or updated, calculated results are saved for the selected scenario and list.

• The selected GL Account is used as the account for the generated results.
• Custom dimensions from the list records are carried into the saved results.
• Results are processed for reporting after the rule change is saved.

Note:

Saved results are not limited to the preview sample. Preview is intended for review before saving.

Deleting a rule

Rules can be removed from the Rules panel.

• The delete action is available on each rule card.
• Removing a rule updates the configured rule set for the selected scenario and list.
• Reporting results are processed again after the rule is removed.

Related topics

• Lists: Review scenario-scoped list records and import lists from another scenario.
• Configuring Targets: Configure Data Loader list targets.
• Web: Review the main areas available in xpna Web.