Formula Logic Help

MatthewWilcox

Hi guys,

 

Got a question I need assistance with.

 

I have a number of lists that follow a hierarchical list, account level 4, 3, 2 ,1. At level 4 each account also belongs to an accounting group (online, local etc) and has a signage value of either 1 or -1.

The accounting group and signage are contained within a module called account lowest level mapping.

I am attempting to create a module consolidates my profit and loss by accounting group instead of the hierarchy that is set up.

 

The module uses the account group dimension and has a line item for amount. The current formula is 'Profit and Loss Module'.Month[SUM: Account Lowest Level Mapping.Account Group] which is incorrect as it does not include the signage. 

If I use 'Profit and Loss Module'.Month[SUM: Account Lowest Level Mapping.Account Group] * lowest level mapping.signage this is incorrect as it needs to multiply each account level 4 item by the signage first, and then sum by account group. 

 

Is this achievable through formulas alone? Or do I need another module to multiply the signage through first which I then sum by.

 

Any assistance would be appreciated.

JaredDolich

@MatthewWilcox 

I'm probably oversimplifying this but could you just use a System module for L4 and create a line item that holds the signing? Then you can look it up anytime you need and for any purpose. If  you need a different sign based on channel (online or local), just make a two dimensional system module.

MatthewWilcox

Hi Jared, thanks for the reply I appreciate it.

 

Im not sure I follow. Ive only completed the level 1 model builder so im still learning the ropes of Anaplan.

 

Just for clarification I have a List for the different account levels 4 rolls up to 3, 3 to 2 etc. I also have a list for the different accounting groups.

 

I have a module for level 4 mapping and within that module contains the signage for each level account level 4 item along with a signage for each of those items.

 

I have a profit and loss module that contains the data for level 4 against month and version.

 

So what I am attempting to do, is for each level 4 item in the profit and loss module, multiply it by the signage in the mapping module and then sum by account group for each month.

 

Does that make sense?

MarkWarren

Take a look at these resources, they may help you here:
Modeling Video Series #2: Signage Convention - Anaplan Community
Changing the Sign for Aggregation - Anaplan Community