## Model Building Problem Solving

Community Manager

## Model Building Problem Solving

Tell us about your time at Anaplan. How long have you been here? What is your current role, and how do you help customers succeed?

Erin Lauterbach: I have been at Anaplan since February 2018. I’m currently on the Global Presales Operations team supporting the Supply Chain LoB. I help customers succeed by building and demoing models to them that showcase the latest capabilities of the Anaplan platform and show them how they can expand their use of Anaplan.

Sam Wong: I’m a Presales Operations Consultant for the Global Presales Operations Team, specializing in Supply Chain. I’ve been here 9 months, and I help customers understand the power, speed, and flexibility of Anaplan through tailored proof of concept models, statistical forecasting applications, and Optimizer use cases and support.

Note: The live Q&A session is now closed.

18 REPLIES 18
Community Manager

## Re: Erin Lauterbach & Sam Wong - Model Building Problem Solving

It's almost time for our next AMA! Erin and Sam are ready to help you problem solve your model building questions. Post your questions here, Monday - Wednesday of next week, or join the conversation live in the forum from 11-12 CST. Our expert planning pair is looking forward to sharing their knowledge with you in this interactive session!

Community Manager

## Re: Erin Lauterbach & Sam Wong - Model Building Problem Solving

The AMA is now open for questions. Post your model building questions any time today, through Wednesday. You can also join them for a live forum discussion this Thursday from 11:00 - 12:00 CST. Erin and Sam are talented Anaplan modelers, ready to share their expertise and discuss best practices with you!

Contributor

## Re: Erin Lauterbach & Sam Wong - Model Building Problem Solving

Hi,

I have a couple of theoretical questions relating to model building.

1. What is the use case of COLLECT()? Why would I want to use it and when would I want to avoid it? It seems to be a powerful function but I'm not sure why it is needed.
2. Any model building tips when working with matrix style hierarchy structures (e.g. country and client dimensions) that have significant amounts of sparsity? Especially interested in output formats, e.g. P&Ls, which give end users the flexibility of having client and country as separate dimensions within the output without the sparsity. For instance, making it very easy for a user to see Client A across all countries, or see performance of England, France and Germany for a given set of clients.

Thanks! Any help is appreciated!

Contributor

## Re: Erin Lauterbach & Sam Wong - Model Building Problem Solving

Question 1 Response:

COLLECT() is a function used in conjunction with Line Item Subsets to dimensionalize line items back to a list.
For example, in our Statistical Forecasting Application, we have a Calculation module that with line items that calculate the final forecasts for each algorithm method.

Step 1: Identify Line Items to Dimensionalize/Collect

Step 2:  Create a Line Item Subset and mark line items for inclusion

Step 3: In your list, create a property called Connect to Subset whose format is your line item subset:

Step 4: Create a module dimensionalized by this Line Item Subset and relevant properties, and call COLLECT()

Step 5: Create a module dimensionalized by your list, and reference the Subset module with a Lookup from the Connect to Subset property

Another example and more details on COLLECT() can be found here: https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/COLLECT.html

Question 2 Response:

Refer to Rob Marshall’s example (link below), he gave a great response and we agree with it. Create a concatenated list of the intersections and use this flat list in your model. Create a module with properties to translate the Flat List to other levels of your hierarchy

https://community.anaplan.com/t5/Best-Practices/Best-practice-for-Organisation-Hierarchy-in-Matrix-S...

- Erin & Sam

Certified Master Anaplanner

## Re: Erin Lauterbach & Sam Wong - Model Building Problem Solving

Hello both,

Same as Nicolas, I have a question around the Collect() function.

Sometimes, we use it to show some KPIs (line items in the original module) and the the year on year growth using the formula "Collect / Collect [LOOKUP: Prior Year] - 1" where Collect is the line item using the formula collect()

The big problem, and I haven't found a solution that is satisfactory, is that we cannot set the summary = formula when using lookup. Hence, the YY% for the full year is not accurate.

Any suggestions? We use collect because we want to show the KPIs as row and the Forecast, Prior Year and Y/Y% as columns.

Occasional Contributor

## Re: Erin Lauterbach & Sam Wong - Model Building Problem Solving

@fabien.junod — Great question!

I was able to get a solution by using the OFFSET() function instead of a [LOOKUP]. This will allow you to set the Summary to Formula.

https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/OFFSET.html?Highlight=offset

In this module view, note that the FY20 result for KPI 1 is the correct result for the year.

This blueprint view shows the formulas and summary method used for the YoY KPI.

--Sam & Erin

Community Manager

## Re: Erin Lauterbach & Sam Wong - Model Building Problem Solving

Today's the day! Erin and Sam are ready to take your questions, starting at 11:00 am CST—just a few hours from now. If you can't join the live hour, post your questions now and check back for answers at a time that's convenient for you.

Frequent Contributor

## Re: Erin Lauterbach & Sam Wong - Model Building Problem Solving

Based on suggestions from the last AMA Pierre Kerkinni & Jon Mavetz - Model Building Solutions I've changed my hierarchy.  Now when trying to build modules that roll up to site sub sets I get an error about common anscestor...which was why I built the hierarchy the way it was before.  We have several hierarchies that roll up to site but don't relate to each other.  Any suggestions would be appreciated.  I'm pasting the new & old hierarchy below:

Old (works but is clunky):

New (get common anscestor error):

Occasional Contributor

## Re: Erin Lauterbach & Sam Wong - Model Building Problem Solving

The common ancestor happens when you share the same parent. We would need some more details on why it’s necessary to dimensionalize the module with a series of lists that all share the same parent. If that is needed then it would be best to revert back to the original hierarchy so that you can have the “Applies To” include the necessary dimensions.

-- Sam & Erin