12.3 Conditional Rules¶
Conditional Rules are the workhorses of data formatting in Datashop. They apply formatting based on the actual values in your data—highlighting what's important, drawing attention to outliers, and making patterns immediately visible.
With Conditional Rules, you can: - Highlight customers with negative profit margins in red - Show green up-arrows for sales growth above 10% - Display traffic lights based on budget variance thresholds
This chapter covers how Conditional Rules work, the operators available, and practical patterns for common business scenarios.
How Conditional Rules Work¶
A Conditional Rule evaluates each value in your visual against one or more conditions. When a value matches a condition, that condition's formatting is applied.
The evaluation process: 1. Datashop calculates the metric values in your visual (e.g., "Profit Margin" for each customer) 2. For each value, the Rule evaluates all Rule Conditions in order 3. Matching conditions merge their Style properties (see 12.2 Rule Conditions and Styles) 4. The final merged style is applied to the visual element (cell, bar, etc.)
Operators and Range Values¶
Conditional Rule Conditions support the following operators:
| Operator | Description | Range Values Required | Example |
|---|---|---|---|
| = | Equals | 1 | Profit Margin = 0 |
| < | Less than | 1 | Profit Margin < 10 |
| > | Greater than | 1 | Index to Last Year > 100 |
| ≤ | Less than or equal | 1 | Sales ≤ 50000 |
| ≥ | Greater than or equal | 1 | Quantity Sold ≥ 1000 |
| between | Within range (inclusive) | 2 | Profit Margin between 5 and 15 |
| not between | Outside range | 2 | Index to Budget not between 95 and 105 |
Data type matching: Range values must match the data type of the column or metric you're evaluating: - Numeric metrics: Use numbers (e.g., 10, 50000, -5.5) - Text dimensions: Use text strings (e.g., "Denmark", "A-Customers") - Date dimensions: Use date values (e.g., 2024-01-01)
Data Source: Metric vs. Column¶
When defining a Rule Condition, you specify what value to evaluate:
Named metric: Evaluate a specific metric regardless of where the Rule is applied. For example, "Profit Margin < 10" always checks the Profit Margin metric, even if the Rule is applied to a different column.
The column it's applied to: Evaluate whatever metric or dimension the Rule is assigned to. This makes the Rule reusable—apply it to "Turnover" and it evaluates turnover values; apply it to "Profit" and it evaluates profit values.
Most Conditional Rules use "the column it's applied to" for flexibility. Use named metrics when you need to format one column based on values in another.
Showing Negative Numbers in Red¶
Number formats can't change the colour, so use conditional formatting:
The quick way: select the column (or the table) in design mode, then HOME → Conditional Formatting → Highlight values → Value < 0 and pick a highlight, e.g. red. The highlights offered are your project's default highlights – see 12.5 System Rules.
With a rule: create a conditional rule with the condition Value < 0 and a style with red font colour, and apply it to the metric. A global rule can be reused in every workbook – see 12.6 Creating Global Rules.
The same menu offers Value = 0, Value > 0 and the same for 100 – e.g. to highlight index values above 100.
Practical Patterns¶
Traffic Light Indicators¶
A common pattern for KPIs is red/yellow/green formatting based on thresholds:
Example: Budget Variance
- Condition 1: Index to Budget < 90 → Red background, bold font
- Condition 2: Index to Budget between 90 and 95 → Yellow background, bold font
- Condition 3: Index to Budget ≥ 95 → Green background, bold font
Performance Trend Arrows¶
Show directional indicators for year-over-year comparisons:
Example: Sales Growth - Condition 1: Index to Last Year ≥ 110 → Green up-arrow shape - Condition 2: Index to Last Year between 90 and 110 → Yellow horizontal-arrow shape - Condition 3: Index to Last Year < 90 → Red down-arrow shape
Color Scales (Heat Maps)¶
Create gradual color transitions from low to high values:
Example: Profit Margin Scale - Condition 1: Profit Margin < 0 → Dark red background - Condition 2: Profit Margin between 0 and 5 → Light red background - Condition 3: Profit Margin between 5 and 10 → Yellow background - Condition 4: Profit Margin between 10 and 15 → Light green background - Condition 5: Profit Margin ≥ 15 → Dark green background
Highlighting Outliers¶
Draw attention to values that exceed normal ranges:
Example: Abnormal Sales Days - Condition 1: Daily Turnover > 100000 → Bold font, blue background - Condition 2: Daily Turnover < 5000 → Italic font, orange background
Data Quality Indicators¶
Flag incomplete or problematic data:
Example: Missing Data - Condition 1: Customer Email = "" → Gray background, italic font - Condition 2: Last Order Date < year=-1 → Yellow background (inactive customer) TODO
Best Practices¶
Order conditions intentionally: Since conditions merge in order, place more specific conditions after general ones. For example, define "< 0" before "< -10" so that the more extreme condition's formatting takes precedence.
Use meaningful ranges: Choose thresholds that reflect actual business logic. A profit margin below 10% might be "concerning" in one industry but acceptable in another. Align your ranges with real performance expectations. Check with the intended audience and peers in the organization what the standard thresholds are for important KPIs.
Test with actual data: Before deploying a Conditional Rule across dashboards, test it with representative data to ensure the ranges and formatting make sense in practice.
Tips – Conditional Rules¶
- Use highlights "as appropriate" and not more. Emphasis on too much at the same time will desensitize users from what really needs attention. And for an executive audience — reserve it for truly exceptional values.
- Remember that multiple conditions can match and merge. This is powerful but requires intentional design.
Common Issues – Conditional Rules¶
Problem: Formatting doesn't appear even though values match the condition
- Solution: Verify that the Rule is actually applied to the correct data element (metric or dimension) in the visual. Check that the data type of your range values matches the column's data type.
Problem: Wrong formatting appears—not what I expected
- Solution: Check the order of Rule Conditions. Later conditions override earlier ones, so ensure your conditions are sequenced correctly. Review the "Automatic" settings in your Style objects—unintended properties may be set.
Problem: Conditional Rule slows down the visual
- Solution: Excessive use of conditional formatting in tables with lots of data points (typically more than 10,000 cells, depending on your hardware) can lead to a noticeable slowdown during display of the results because formatting affects each cell's size and thus requires a lot of processing. If this becomes problematic, you have various options:
- Reduce the number of conditional formatting rules in the table
- Reduce the number of cells affected by conditional formatting, i.e. make the condition's range less inclusive
- Reduce number of data points in the table by narrowing down filter criteria or by reducing the number of dimensions and metrics