Adding of column values

Hi,

I have a column which is made up from a List. I've created a "new column" called "Sales and Marketing" and would like to sum up the values that are currently in "Sales" and "Marketing" respectively. The values in the other columns remain as it is. Is there a formula that is able to do this?

Structure of the module as below.

Accepted answers

Vinay VaradarajM

Hi @DarrenS ,

Although, I would recommend following Parent-Child relationship and then adjusting the downstream calculations, here is the alternate way which you might be looking for (if there are intricacies which prevent the use of parent-child arrangement in your case)

You can sum the values in Sales, Marketing by using a property (Parent item) as shown below:

Data View

Screenshot 2021-05-25 at 12.19.32 PM.png

Blueprint View

Screenshot 2021-05-25 at 12.35.25 PM.png

The formula in lineitem: Output is using Admin Settings module. This is to avoid hard-coding in the formula:

Screenshot 2021-05-25 at 12.20.03 PM.png

These 3 lineitems in Admin Settings are LOB list formatted.

Hope this helps!

Best Regards,

vinayvm

All comments

Vinay VaradarajM

Hi @DarrenS ,

You can achieve this by having the list elements: Sales, Marketing as child items of 'Sales & Marketing'. This arrangement will automatically sum the values at the parent level, without having to use a formula.

Below is a screenshot for your reference.

Hope this helps!

Best Regards,

vinayvm

DarrenS

Hi @Vinay VaradarajM

Thanks for your help. It works! 🙂

But is there another method using a formula? Reason being, after i've made the amendment on the list, there is a line item which uses this LOB list and the values are kind of wrong now.

Vinay VaradarajM

Hi @DarrenS ,

You can sum the values in Sales, Marketing by using a property (Parent item) as shown below:

Data View

Screenshot 2021-05-25 at 12.19.32 PM.png

Blueprint View

Screenshot 2021-05-25 at 12.35.25 PM.png

The formula in lineitem: Output is using Admin Settings module. This is to avoid hard-coding in the formula:

Screenshot 2021-05-25 at 12.20.03 PM.png

These 3 lineitems in Admin Settings are LOB list formatted.

Hope this helps!

Best Regards,

vinayvm

DarrenS

Hi @Vinay VaradarajM ,

Regards to below solution you had provided earlier, I just realized an issue with the total sum of All LOBs.

Due to the hierarchy list of LOB, the total sum of LOB is counting the "Sales" and "Marketing" figures as well.

Is there a way to exclude double counting the respective "Sales" and "Marketing" figures. I will be hiding the "Sales" and "Marketing" columns and the "All LOBs" should be showing 325 instead of 525.

Vinay VaradarajM

@DarrenS ,

I like to go back to the initial recommendation of using parent-child relationships which would automatically sum up the values at top level.

However, a quick workaround would be to introduce a dummy list item and use it as follows:

Screenshot 2021-07-14 at 6.38.11 PM.png

> You can hide the Dummy list item in the page as needed

> Also, you might want to use a Line item Subset with items other than Dummy item in downstream modules to prevent users from accessing this item (to avoid confusion)

Regards,

vinayvm

DarrenS

Hi @Vinay VaradarajM ,

Thanks for this.

How do you set up the Line item Subset with items other than Dummy item and what do you meant by downstream modules?

Vinay VaradarajM

@DarrenS ,

Sorry, I meant list subset but ended up typing Line item subset : P

The list subset will contain all list items apart from the dummy item, and you can use this list subset as a dimension in your modules instead of the whole list (to keep users from getting confused)

Regards,

vinayvm

DarrenS

@Vinay VaradarajM ,

Do I create a subset list that contains "Other, Supply Chain, HR, Projects, Finance, Sales and Marketing" or "Other, Supply Chain, HR, Projects, Finance, Sales, Marketing, Sales and Marketing". It should be the first one right?

With the subset list, does it mean that it will just gives us the total sum of the subset list rather than the whole list? Is there still a need to create the dummy line item?

Vinay VaradarajM

@DarrenS ,

- Inclusion of Sales, Marketing, Sales & marketing in the subset will depend on your objective/process. If you want users to input values, you might want to include just the first two from the above. Whereas If it is a report, you might want to include all the 3.

- The purpose of list subset is that you will be able to prevent users from seeing the 'Dummy' item and getting confused, Also, the Dummy line item in your module will be necessary if you want the total to match at 'All LOB' level.

Regards,

vinayvm

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.…