Driving a new age of connected planning Mapping

Driving a new age of connected planning

Mapping data SUM and LOOKUP

What are they and What do they Do SUM • • vs SUMIF Summing transaction data Many-to-one mapping Also one-to-one mapping LOOKUP • VLOOKUP / HLOOKUP / LOOKUP • Top-down assumptions • One-to-many mappings • Also one-to-one mapping Both • • Used when dimensionality doesn’t match System module provides transformation

Simple SUM

Data: Revenue by Location & Product Ask: Summarize by Product Size Data Ask

System Module holds Size attribute of Products (not part of the hierarchy)

“Visualize” the mapping Orient the source and target for the dimension you are mapping Source Mapping Target 'REV 03 Margin Calculation'. Revenue[SUM: 'SYS 06 Product Details'. Size]

Simple rule for which to use: Compare source and mapping dimensions Source Mapping If the source is the same then it’s a SUM

Simple LOOKUP

Data: Bonus % by Department Ask: Apply Bonus % to Employee (Department is a parent of Employee) Data Ask

System Module holds Size attribute of Employee (not part of the hierarchy)

“Visualize” the mapping Orient the source and target for the dimension you are mapping Source Target Mapping Target 'EMP 03 Employee Drivers'. Bonus %[LOOKUP: 'SYS 08 Employee Details'. Department]

Simple rule: Compare source and mapping dimensions If the source is NOT the same, then it’s a LOOKUP

Multiple SUMs

Data: Revenue by Product and Location (non-hierarchical lists) Ask: Summarize by Country and Product Family Data Ask

Two SUM expressions using different System modules SUM: 'SYS 06 Product Details'. Product Family SUM: 'SYS 04 Location Details'. Country 'REV 03 Margin Calculation'. Revenue[SUM: 'SYS 06 Product Details'. Product Family, SUM: 'SYS 04 Location Details'. Country]

Multiple LOOKUPs

Data: Bonus % by Department and Country Ask: Apply Bonus % to Employee (Department is a parent of Employee) Data Ask

Two LOOKUP expressions using the same System module, different attributes LOOKUP: 'SYS 08 Employee Details'. Department LOOKUP: 'SYS 08 Employee Details'. Country 'EMP 02 Employee Drivers'. Bonus %[LOOKUP: 'SYS 08 Employee Details'. Department, LOOKUP: 'SYS 08 Employee Details'. Country]

Wrapping it up!

Tying it all Together When in doubt • Don’t guess! • Dimensions match—easy • Same lists across both target and source • Dimensions don’t match • Visualize (in Anaplan, or paper!)

Best practices Don’t include SUM and LOOKUP in the same expression [. . ] • Intermediate calculation needed can cause performance issues • Split out the SUMs first into separate modules/line items Use SUMs to save calculations in multiple hierarchies • Native aggregation is quicker but. . . • If only some of the aggregations are needed, turning off summaries and using [SUM: …] is more efficient Use LOOKUPs instead of SELECT and multiple IFs • But don’t hard-code the parameters (use a LOOKUPs module) • [LOOKUP: Time. FY 19] • [LOOKUP: Products Family. Chocolate]