Min-Max Rule

Set up min/max rules in your Lumel planning sheet to reflect financial constraints such as budget caps, approved ranges, or regulatory thresholds.

Why do we need min/max rules in reports?

Min/max rules can prevent entries such as percentages above 100, or budgets that fall outside the specified business range. Catching out-of-range values reduces corrections and rework down the line. Outliers caused by data entry errors can skew trends and KPIs - min/max rules help avoid false alarms.

In this section, we'll look at how to configure min/max rules. Lumel Planning supports 3 types of rules:

• Static values: Enter the minimum and maximum values that can be entered.

• Measure-based: The range is calculated based on the minimum and maximum values in the selected measure.

• Variable-driven: Min/max values can be controlled by variables.

Types of values in min/max rules

Step-by-step guide to setting up min/max rules

1. Min/max rules with static values

In this example, the interest and rebate rates are percentages. We'll look at how to enforce a numeric range rule for these fields.

STEP 1: Choose the Selected Measures from the Apply to dropdown and select the Interest Rate and Rebate Rate options from the Choose Measures dropdown.

STEP 2: Select the Static option from the Minimum Value and Maximum Value section.

Apply the rule to selected measures and choose static option

STEP 3: Enter the values that will define the upper and lower bounds of the acceptable range. We have set the lower limit to 1.25 and the upper limit to 18.95.

Assign the upper and lower limits

STEP 4: Select the Custom option and select the target rows and columns the rules should be applied to. We've selected only the child rows and excluded the total and grand total rows and columns.

Row selections to exclude totals

Column selection to exclude totals

Apply rules to specific rows and columns

Notice how values outside the defined range are not accepted.

Lumel planning throws an error when values outside the defined range are entered

2. Min/max rules with variables

Another option is to control the minimum and maximum values with variables.

STEP 1: Define numeric variables to control the minimum and maximum limits.

Define variables to control minimum and maximum values

STEP 2: Select Variable and choose the variables defined in STEP 1 in the Minimum Value/Maximum Value section.

Select the pre-defined variables that will be used to control the value range

STEP 3: In the Rows Selection section, let's select Custom and choose the child categories. The range rules will apply only to the selected child rows. This step is optional and depends on specific requirements.

Select the rows to apply rules

With variables, the upper and lower limits can be controlled dynamically, as shown in the animation.

Use variables to control min and max values

Configuration options for min/max rules

Basic configurations are covered in the Data validation rules section. The options specific to mix/max rules are covered in this section.

2.1. Set exclusive

Use Set Exclusive to exclude boundary values, ensuring the minimum is greater than and the maximum is less than the specified limits - for example, when the minimum value must be greater than (but not equal to) the lower limit, and the maximum value must be less than (but not equal to) the upper limit.

Enable set exclusive option

In the animation, notice how the visual does not accept 1.25 although it is the lower bound. This is because the Set Exclusive option has been enabled.

Error while entering the lower bound value when set exclusive is enabled

2.2. Minimum/Maximum value

In the first section, we used static values to set the upper and lower limits. You can also use measures from your report as the min/max values.