Using a IF THEN ELSE formula and LOOKUP formula to solve a Customer Buying Group mapping question

gcaldis

Hi there

 

My organisation's Anaplan model was originally built to cater for a "Bottom Up" sales plan , and I am amending it to also cater for a "Top Down" sales plan, when required.

 

We have 2 modules in this model, TOP DOWN and TOTAL SALES.

 

TOP DOWN - Only has one Customer Buying Group code called "TDB" - short for Top Down Budgeting.

TOTAL SALES - Has multiple Customer Buying Group codes (e.g. "KO3", "M04"  etc)

 

Once particular problem I have is how to write the expression which says.....

 

IF the "TOP DOWN" module's line item "Customer Buying Group" code of "TDB" is = 0 (i.e. $0 sales),

THEN show the "TOTAL SALES" Customer Buying Group code (e.g. K03, M04) Sales $ amounts,

ELSE take the Sales $ amount  from Customer Buying Group "TDB" (in which case all other codes e.g. K03, M04 etc should show $0 Sales).

 

Would I need to reference an ITEM or LOOKUP formula within a normal IF THEN ELSE formula to achieve this?

 

Thanks

George

JaredDolich

@gcaldis George,

Awesome use case, and nicely written up. Here's an idea:

• Create a system module for the buying groups.
• Create a BOOLEAN line item called "TBD Related?"
• Create a list formatted line item called "Buying Group" using the buying group list.
• Go into grid mode and set the TBD? to TRUE for the all the buying groups that you want to sum up if TBD is zero.
• Add another line item in TOTAL SALES called "Sales Replace" = IF TBD Related? = TRUE then Sales ELSE 0. This will sum up only the buying groups you want.
• We already know the TOP DOWN module is only for TBD so we don't have to do anything special to find it
• In the TOPS DOWN module add a line item "New Sales" = If Sales = 0 then TOTAL SALES.Sales Replace[SUM: System Module.Buying Group] ELSE Sales

This is a little rough. If you run into any issues, send a note back here and I'll create an example for you in Anaplan. 

Sounds like a really neat use case. Let's get this!