Sum data from a module with line items to another module with a matching list

Could someone suggest a solution? screenshots attached also.

I have a module (PER01) where I have these line items - the data is by employee (from employee list):

- base salary

- incentive scheme

- other social costs

I have another module (PER02), where I want the above data to fit in with only 1 line item called "input" & dimension accounts list. So in this, the accounts are in a list, not a line item. There are more accounts in the list than the 3 above mentioned ones, which is OK because in actual version, I will need those extra accounts. I noticed that I can write formula and mark the scope to be "all versions but actual", so actual version can still be with data input.

- I added versions dimension to this module - I will only need to get the data into the budget version.

- I added Cost Center dimension to this module

- Employees are not a dimension in this module, but each employee belongs to a cost center so it should be possible to sum the figures.

I have also created an intermediate module (PER03),a matrix, where I connect the line items from the first module (PER01), to the list in the 2nd module (PER02). But I'm kind of stuck here - I'm not sure if this 3rd module is needed, or can I manage without it..

How can i get the data into PER02 by cost base, for budget version from PER01?

Can someone suggest a formula?

Thank you

Kinga

PER01_102874.png

PER03_102874.png

PER02_102874.png

Accepted answers

alexpavel

@kinga.markovics Having a numbered list, referring directly to a particular element in a formula... there are some "best practices" you go against and you will close some doors. 🙂

For example, you will not be able in the future to flag the list "AC02 Sub-Accounts selected" as Production Data.

But, in the end, the formula will work.

The reason why your formula does not work is that the Anaplan Versions list does not accept the ITEM() function and a way to check for a version if is Budget is to create a system module dedicated to Versions and flag the Budget version for a boolean line-item like "Budget?"... and in the formula, just refer to the line-item Budget? (see below)

Example:

IF SYS01 Versions.Budget? THEN

IF ITEM(AC02 Sub-Accounts selected) = 'AC02 Sub-Accounts selected'.'#144' THEN 'PER01 Salaries Plan'.Base Payroll ELSE 0

ELSE

alexpavel

I totally agree that there are situations (especially with HR costs use cases) where there is needed to switch between line-items and lists.

I always try in these cases to avoid the hard-coding and use the line-item subsets with a mapping module.

I think you wanted to achieve this parametrization with your mapping module PER03.

A solution could be like this, using line-item subsets and a mapping module:

1. create a line-item subset from PER01 line-items

2. Create a "mirror" module PER01.b where you collect data from PER01 (so data from PER01 will be duplicated for the only reason to change the layout of data).

3. Have a mapping module PER03 where you can map the Line-item subset from point 1 with the Accounts list

4. Summarize data in PER02 from PER01.b using the mapping table PER03

Below the simplified examples:

PER01.b to collect data from PER01

Mapping module PER03

Final module PER02 to have data in 1 line-item with the Accounts list.

Just be aware that the line-item subsets work only on numeric formatting line-items.

Hope it helps

Alex

All comments

alexpavel

@kinga.markovics The most direct way to solve this ( not very clean and quite hard-coded) is to use this kind of formula in PER02 ( not really necessary PER03 mapping module).

I notice that in PER02 you also have Version...you will need to add some IF in the below formula in order to associate the data to the correct Version.

IF ITEM(Accoutns list) = Accoutns list.Base Salary THEN
'PER01'.Base Salary
ELSE IF ITEM Accoutns list) = Accoutns list.Incentive Scheme THEN
'PER01'.Incentive scheme
ELSE IF ITEM(Accoutns list) = Accoutns list.Other Social Costs THEN
'PER01'.Other Social Costs
ELSE
0

Being numeric line-items in PER01 and if you want to make it more parametrical you could also create a line-item subset and create the mapping module using the line-item subset from PER01.

Hope it helps

Alex

kinga.markovics

Hi @alexpavel, I think that should work thank you.

For now, i removed my cost center dimension to make it simpler and have only versions as extra.

I added the IF for version and shortened the formula for only 1 of the accounts (base payroll) to test it out, but it's not accepted ("input is invalid")

IF Versions=Versions.Budget AND ITEM(AC02 Sub-Accounts selected) = 'AC02 Sub-Accounts selected'.'#144' THEN 'PER01 Salaries Plan'.Base Payroll ELSE 0

'AC02 Sub-Accounts selected'.'#144 --- this is Base Payroll account from the accounts list.

'PER01 Salaries Plan'.Base Payroll --- this is the line item Base Payroll

Is there something wrong with my formula writing?

Regards

Kinga

alexpavel

@kinga.markovics Having a numbered list, referring directly to a particular element in a formula... there are some "best practices" you go against and you will close some doors. 🙂

For example, you will not be able in the future to flag the list "AC02 Sub-Accounts selected" as Production Data.

But, in the end, the formula will work.

Example:

IF SYS01 Versions.Budget? THEN

IF ITEM(AC02 Sub-Accounts selected) = 'AC02 Sub-Accounts selected'.'#144' THEN 'PER01 Salaries Plan'.Base Payroll ELSE 0

ELSE

kinga.markovics

@alexpavel, would you have a better way to build this?

the whole model is for cost center reporting (actuals) & planning (budgeting):

we have account (e.g personnel costs) and subaccounts (salaries, social costs, bonus etc) in 2 non-related lists, and then use a module to map which subaccounts should be under which accounts. We then have a 3rd list, which is imported from the module using the mapping of accounts & subaccounts. This was done to avoid having so many unncessary account & subaccount combinations (selected accounts).
we divided the selected accounts for "personnel" and "other costs" subsets.
- for personnel, we need to have the planning done on employee level who each belong to 1 cost center (budget) but actuals come through import (which is possible with the formula scope)
- for other costs, it's done on cost center level, similarly the budgeting is done manually, and actuals come through import.(which is possible with the formula scope)

It didn't seem possible to have formula for some accounts (from a list), but input for the others --that's why we made the 2 subsets

As a result, our plan is to have 4 modules

module for personnel planning - here we need to set some formulas based on the country of the employee (social cost %, increase % etc), while other lines need to be input (base pay). So these needed to be line items.
module for retrieving personnel costs - here we'll use the personnel subset list account and "input" line item to import actuals, and retrieve budget from the 1st module
module for other costs - here we'll use other subset list account and "input" line item to import actuals & input budget
and a module to consolidate and take data from 2. and 3.

Not a very simple model 😃

alexpavel

I totally agree that there are situations (especially with HR costs use cases) where there is needed to switch between line-items and lists.

I always try in these cases to avoid the hard-coding and use the line-item subsets with a mapping module.

I think you wanted to achieve this parametrization with your mapping module PER03.

A solution could be like this, using line-item subsets and a mapping module:

1. create a line-item subset from PER01 line-items

2. Create a "mirror" module PER01.b where you collect data from PER01 (so data from PER01 will be duplicated for the only reason to change the layout of data).

3. Have a mapping module PER03 where you can map the Line-item subset from point 1 with the Accounts list

4. Summarize data in PER02 from PER01.b using the mapping table PER03

Below the simplified examples:

PER01.b to collect data from PER01

Mapping module PER03

Final module PER02 to have data in 1 line-item with the Accounts list.

Just be aware that the line-item subsets work only on numeric formatting line-items.

Hope it helps

Alex

kinga.markovics

I got it working your way, thank you very much.

so this way, the account list can be production data right?

Regards

Kinga

alexpavel

yes. correct. if the formulas do not reference elements from a list except for the Top-level element, that list will be able to be setup, if necessary, as "Production Data"

Quick Links

Can you help?

Unanswered questions

Update email id
Can anyone please explain how to update my registered email ID jn Anaplan.
Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
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.…