Formula Logic Help

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.

Accepted answers

All comments

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

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…