Adding of column values

DarrenS

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. 

 

 

 

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

 

Blueprint View

 

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

These 3 lineitems in Admin Settings are LOB list formatted.

 

Hope this helps!

 

Best Regards,

vinayvm

 

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.

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:

 

> 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