1 / 22

Mapping data SUM and LOOKUP

Mapping data SUM and LOOKUP. VLOOKUP / HLOOKUP / LOOKUP Top-down assumptions One-to-many mappings Also one-to-one mapping. SUMIF Summing transaction data Many-to-one mapping Also one-to-one mapping. What are they and What do they Do. SUM. vs. LOOKUP. Both.

dnoma
Download Presentation

Mapping data SUM and LOOKUP

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Mapping dataSUM and LOOKUP

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

  3. Simple SUM

  4. Data: Revenue by Location & ProductAsk: Summarize by Product Size Data Ask

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

  6. “Visualize” the mappingOrient the source and target for the dimension you are mapping Mapping Target Target Source 'REV03 Margin Calculation'.Revenue[SUM: 'SYS06 Product Details'.Size]

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

  8. Simple LOOKUP

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

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

  11. “Visualize” the mappingOrient the source and target for the dimension you are mapping Target Mapping Source Target 'EMP03 Employee Drivers'.Bonus %[LOOKUP: 'SYS08 Employee Details'.Department]

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

  13. Multiple SUMs

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

  15. Two SUM expressions using different System modules SUM: 'SYS06 Product Details'.Product Family SUM: 'SYS04 Location Details'.Country 'REV03 Margin Calculation'.Revenue[SUM: 'SYS06 Product Details'.Product Family, SUM: 'SYS04 Location Details'.Country]

  16. Multiple LOOKUPs

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

  18. Two LOOKUP expressions using the same System module, different attributes LOOKUP: 'SYS08 Employee Details'.Department LOOKUP: 'SYS08 Employee Details'.Country 'EMP02 Employee Drivers'.Bonus %[LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Country]

  19. Wrapping it up!

  20. 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!)

  21. 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.FY19] [LOOKUP: Products Family.Chocolate]

More Related