EXPAND

EXPAND

Description

Expands the Node by the desired Levels. The result is a cross product of all rows of the Node with all values of the Level entered in the formula.

Signature

EXPAND(NodeLevel1 [, Level2, …])

Parameters

  • Node: Input node, specified using the node name in single quotes (e.g. 'Profit')
  • Level: One or more level from different dimensions that do not exist on the input node

Examples

Input = 

Value
1.1


EXPAND('Input',"Year") = 

Year Value
2017 1.1
2018 1.1
2019 1.1
... 1.1
EXPAND can take multiple levels. Models often use constructs like EXPAND(1.1,"Year","Product") to create the right multidimensional cube. Be careful though to not create very large results - the performance may suffer. As a precaution, the result of an EXPAND operation cannot be larger than 100 million rows.

EXPAND('Input',"Year","Product") = 

Year Product Value
2017 A 1.1
2017 B 1.1
2018 A 1.1
2018 B

1.1

... A 1.1
... ... 1.1

Use Case

We want to add delivery costs to our Variable Costs per product, but the granularity of the delivery costs are define at higher level.

Delivery Costs =

Product Line Delivery Costs
M 100
Z 200


Variable Costs w/o Delivery =

Product Line Product Variable Costs
M M1 750
M2 2250
Z Z3 3000


ADDITION (+)  with Non-Overlapping Dimensions  leads to loss of the product dimension with delivery fee added only once.

'Delivery costs'   + 'Variable Costs w/o Delivery' = 

Product Line OPEX
M 750 + 2250 + 100 = 3100
Z 3000 + 200 = 3200


Using expand, the delivery costs are expanded to the product level and we get the expected result.

 EXPAND('Delivery Costs',"Product")  + 'Variable Costs w/o Delivery' = 

Product Line Product Totals
M M1 750 + 100 = 850
M2 2250 + 100 = 2350
Z Z3 3000 + 200 = 3200



(lightbulb) If you need only one single value, take a look at EXPANDSINGLE.

Was this helpful?

Yes | Somewhat | No