Adding of column values

DarrenS
Occasional Contributor

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. 

DarrenS_0-1621913765160.png

 

DarrenS_1-1621913777249.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
vinayvm
Certified Master Anaplanner

Re: Adding of column values

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

View solution in original post

9 REPLIES 9
vinayvm
Certified Master Anaplanner

Re: Adding of column values

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.

Screenshot 2021-05-25 at 10.41.08 AM.png

Hope this helps!

 

Best Regards,

vinayvm

DarrenS
Occasional Contributor

Re: Adding of column values

Hi @vinayvm 

 

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.

vinayvm
Certified Master Anaplanner

Re: Adding of column values

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

DarrenS
Occasional Contributor

Re: Adding of column values

Hi @vinayvm ,

 

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. 

 

 

vinayvm
Certified Master Anaplanner

Re: Adding of column values

@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
Occasional Contributor

Re: Adding of column values

Hi  @vinayvm ,

 

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? 

vinayvm
Certified Master Anaplanner

Re: Adding of column values

@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
Occasional Contributor

Re: Adding of column values

@vinayvm ,

 

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? 

vinayvm
Certified Master Anaplanner

Re: Adding of column values

@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