writing-pigment-formulas

What this skill does

# Writing Pigment Formulas

This skill provides comprehensive guidance for writing formulas in Pigment's multidimensional formula language, including formula builder tools for validation and generation.
Pigment uses proprietary formula language which should never be confused with other language.
Never mix Pigment formula language with other language, and never assume you know the language before reading this documentation.

**CRITICAL - ABSOLUTE PROHIBITION**: Pigment has its own unique formula language.
You MUST NEVER write code or functions using another language being Excel, SQL, Python, JavaScript, MDX, DAX, or ANY other programming or query language.
ONLY Pigment syntax exists when writing formulas.

## When to Use This Skill

- **Write formulas** - Creating calculations for metrics and list properties
- **Aggregate data** - Rolling up from transaction lists or detailed dimensions
- **Perform time-series calculations** - YTD, rolling averages, sequential logic
- **Use functions** - CUMULATE, SHIFT, ITEM, MATCH, TIMEDIM, etc.
- **Use modifiers** - BY, ADD, REMOVE, SELECT, FILTER, EXCLUDE, TOPARENTLIST, TOSUBSET
- **Debug syntax** - Troubleshooting formula errors
- **Test and validate formulas** - Verifying formula correctness and expected behavior
- **Transform dimensions** - Changing dimensional structure of calculations
- **Allocate data** - Distributing values across dimensions
- **Match and lookup** - Finding data across dimensions

---

## Syntax Fundamental

**Quoting Rules - MUST FOLLOW:**

| Element         | Syntax                        | Example                                           |
| --------------- | ----------------------------- | ------------------------------------------------- |
| Metric names    | Single quotes                 | `'Revenue'`, `'Total Sales'`                      |
| Dimension names | Single quotes                 | `'Product'`, `'Country'`                          |
| Property access | Dot notation with quotes, chainable | `'Product'.'Category'`, `City.Country.Currency` |
| Dimension items | Double quotes after dimension — **MP02:** literal only in `VAR_` default | `Month."Jan 25"` only when setting a `VAR_` metric default |
| String values   | Double quotes                 | `"Active"`, `"Completed"`                         |

**Cross-app references:** 
- A block from another application can only be referenced if it has been shared through a Library and that Library is activated in the current application. 
- The syntax is `'APPLICATION_NAME'::'BLOCK_NAME'`. 
- If the block name is unique across all activated libraries the application prefix may be omitted, but always use the full form for clarity.

**No hard-coding (MP02 — hard constraint):** See [modeling_principles §4](../modeling-pigment-applications/modeling_principles.md). Before member-specific or time-bounded formulas, read [formula_writing_workflow.md](./formula_writing_workflow.md) Step 2 and [formula_modifiers.md](./formula_modifiers.md) (FILTER, SELECT, BY CONSTANT).

**Common Mistakes:**

- ❌ `Revenue` → ✅ `'Revenue'` (missing quotes)
- ❌ `Product.Category` → ✅ `'Product'.'Category'` (missing quotes)
- ❌ `Month.'Jan 25'` → ✅ `Month."Jan 25"` (items use double quotes; in formulas use a `VAR_` metric per MP02)

---

## Performance Patterns

**Apply this checklist proportionally to formula complexity.** Simple arithmetic between existing same-dimensioned metrics (e.g. `'A' + 'B'`, `'A' * 'B'`, `'A' / 'B'`) needs no performance wrapping — deliver as-is. Use the checklist as a review gate for formulas that introduce conditionals, dimensional changes, date-range logic, or that target large/sparse metrics.

Read [formula_performance_patterns.md](./formula_performance_patterns.md) and verify:

**Always check (universal):**

- [ ] Identifiers are correctly quoted (single quotes for names, double quotes for items)
- [ ] Dimensions are aligned — no unintended ADD or dimension mismatch
- [ ] Scoping clauses appear FIRST (FILTER, EXCLUDE, IFDEFINED)
- [ ] Aggregations (REMOVE, BY) appear AFTER calculations

**Check when conditionals are present:**

- [ ] Using IFDEFINED instead of IF(ISBLANK()) for existence checks
- [ ] Using IFBLANK instead of IF(ISBLANK(...), default, ...) for defaults
- [ ] Conditional creation: use IF (not ADD + FILTER); subsetting a computed expression: use FILTER: CurrentValue (not IF(expr, expr, BLANK))

**Check when date ranges are defined by Start/End:**

- [ ] Avoid multi-conditional IFs (`Date >= Start AND Date < End`) when PRORATA semantics apply
- [ ] Prefer `PRORATA()` to express "active within a date range" and derive booleans or numeric flags from `PRORATA()` using ISDEFINED/IFDEFINED

**Check when prior period lookups are needed:**

- [ ] Using SELECT for prior period lookups (NOT PREVIOUS)

**Check when dimensional changes or mappings are involved:**

- [ ] Using BY instead of ADD where mapping exists
- [ ] If you BY on a dimension-typed metric, do not add IF/ISBLANK guards; BY respects that metric's sparsity

**Check when the metric is large/sparse or involves access rights:**

- [ ] Avoid ISBLANK/ISNOTBLANK on large sparse metrics — use ISDEFINED/IFDEFINED
- [ ] Use BLANK instead of 0 for empty values (see exception below for meaningful zeros)
- [ ] Use BLANK instead of FALSE for boolean flags (FALSE is stored, BLANK is not)
- [ ] Access rights wrapped in IFDEFINED(User, ...)
- [ ] **MP02:** No `Dimension."Item"` in formulas; no `DATE(...)` for planning bounds; relative metric names only — see [formula_writing_workflow.md](./formula_writing_workflow.md) Step 6 checklist

For the full date-range presence pattern (PRORATA worked examples, ISDEFINED/IFDEFINED derivation, when simple IF is acceptable), see **Pattern 11** in [formula_performance_patterns.md](./formula_performance_patterns.md).

---

## Formula Writing Process

**Key phases**: Understand Context → Search Documentation → Design → Build → Optimize → Validate → Deliver

**Follow the complete 8-step workflow**: [./formula_writing_workflow.md](./formula_writing_workflow.md)

- **Critical**: Always search documentation first before writing
- **Governance check (MP02 — required):** [modeling_principles §4](../modeling-pigment-applications/modeling_principles.md); Version patterns: `skill:planning-cycles-pigment-applications`.
- **Validation & Delivery**: Use Formula Builder Tools to validate and deliver formulas

---

## Formula Validation and Building Tools

**Important**: These tools are for **validation and implementation** when working with real formulas.

### Quick Validation

- `tool:validate_formula` - Validate formula syntax WITHOUT applying it to any block
  - Use for: Checking syntax before calling `tool:update_list_property_formula`
  - Use for: Ensuring formula syntax is correct before including in user messages
  - Input: `formula` (the Pigment formula text)
  - Returns: Validation result with error highlighting and hints if invalid
  - **Limitations**:
    - Do NOT use with formulas containing `Previous` or `PreviousOf` functions

**Recommended Workflow**:

1. **Draft formula** - Write your formula based on requirements
2. **Validate** - Use `tool:validate_formula` to check syntax
3. **Fix errors** - Iterate until formula is valid
4. **Apply** - Use `tool:create_or_update_formula` or `tool:update_list_property_formula`

**How to apply**: After validation, use:

- Metrics: `tool:create_or_update_formula` with the formula
- List properties: `tool:update_list_property_formula` with the formula

---

## Prerequisites

This skill focuses on formula **implementation**. Before writing formulas, understand foundational concepts from the **modeling-pigment-applications** skill:

- Core platform knowledge (multidimensional engine, dimensions vs properties, sparsity principles)
- Pigment Modeling Best Practices standards (sparsity preservation, dimension alignment, formatting)
- Dimensional design concepts (source-to-target relationships, transformation cases