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,

You can achieve this by having the list elements:Sales,Marketingas child items of'销售与市场营销'。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 · Accepted solution]

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

蓝图视图

The formula in lineitem:Outputis usingAdmin Settingsmodule. 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

[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)

问候，

vinayvm

[DarrenS]

Hi@Vinay VaradarajM,

Thanks for this.

How do you set up theLine 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)

问候，

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?

列表的子集,这是否意味着它将居st 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,

- 在子集中包含销售，营销，销售和营销将取决于您的目标/过程。如果您希望用户输入值，则可能只需要在上面包含前两个。而如果是报告，则可能需要包括所有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.

问候，

vinayvm