Dimensionality & hierarchies

Use this category when you need to control a formula’s output shape: which dimensions and levels the result has, and at what granularity values exist.

These functions let you aggregate to higher levels, remove detail, expand values to more detailed intersections, or distribute values across level values.

Start here if…

• Your result is too aggregated and you lost more detail than expected
• Your result is too granular and contains more detail than expected
• Two nodes won’t combine cleanly because they have different dimensionality
• You want to prepare a node for simulation by creating the full dimensional space

Not here if…

• You only need a numeric transformation such as scaling, rounding, or ratios → see Math & numeric
• You only need a subset of level values → see Filtering & data shaping

Mental model

• Roll up: aggregate to higher levels
• Drop: remove levels from a dimension to simplify the shape
• Expand: add detail by creating combinations of level values
• Distribute: allocate values down to more detailed level values using a distribution key
• Rebook: move values between level values within the same level

Common patterns

Aggregate to a higher level ROLLUP('Node', "Dimension", "Level") Use when you want values only at selected higher levels.

Make one node match another node’s shape ROLLUP_TO('Node', 'TargetShapeNode') Use when you want the first node aggregated to the second node’s level dimensionality.

Remove unwanted granularity DROPLEVEL('Node', "Level") Use when a node carries detail you don’t want in further calculations.

Expand to more detailed intersections EXPAND('Node', "Level") Use when you need a value available at a more detailed level.

Expand only to specific level values EXPANDSINGLE('Node', "Level", ["A", "B"]) Use when you want targeted expansion without creating the full cross product.

Create an empty data space for simulation EXPAND(0, "Level1", "Level2") Use when you want to initialize a node with the required dimensionality.

Distribute totals down to detail using weights DISTRIBUTE('OriginalNode', 'DistributionNode') Use when you want allocations that preserve totals but add detail.

Move values between level values REBOOK('Node', "Level", "OldValue", "NewValue") Use when you need to reclassify values within the same level.

Functions in this category

Choosing between similar functions

ROLLUP vs DROPLEVEL

• Use ROLLUP when you want an explicit aggregation target
• Use DROPLEVEL when you want to remove specific granularity from a dimension

EXPAND vs EXPANDSINGLE

• Use EXPAND when you want the full cross product for a level
• Use EXPANDSINGLE when you only want specific level values

ROLLUP vs ROLLUP_TO

• Use ROLLUP when you know the target levels
• Use ROLLUP_TO when another node already defines the target shape

DISTRIBUTE vs REDISTRIBUTE

• Use DISTRIBUTE to allocate values down using a distribution key
• Use REDISTRIBUTE when allocation should happen only within defined clusters

REBOOK vs DISTRIBUTE

• Use REBOOK when you want to move values from one level value to another
• Use DISTRIBUTE or REDISTRIBUTE when you want to split totals across multiple level values using weights

Pitfalls & troubleshooting

• Result is too aggregated: check whether + or - aligned to shared dimensions; make the intended shape explicit with ROLLUP or ROLLUP_TO
• Result is too granular or too large: check whether * or / expanded across combined dimensions or whether an EXPAND added more detail than intended
• Result is empty after reshaping: confirm referenced levels/level values exist and labels match exactly (spelling/case)
• Unexpected allocation results: verify the distribution key node has values at the target detail and that totals behave as expected after distribution
• Element-wise adjustment needed: use ADDEACH rather than relying on + constant behavior

Related sections

• Filtering & data shaping: subset selection and fill or pick helpers
• Operators: alignment behavior and comparisons used inside formulas
• Troubleshooting guide: wrong shape and missing values
• Function catalog: full signatures, parameters, and examples