How can I 'sum' non numeric fields to pull data through from one module to another?

HeidiKing

I am trying to pull non numeric data through from one module to another. I have found that "If your mapping table contains the Source List and maps to a Target List-formatted Line Item or List-formatted Property, then use SUM in your formula." but how can I pull this information through when the data I need is text formatted and not numerical? 

 

I don't want to build a huge re-staging module unless absolutely necessary....

DavidSmith

@HeidiKing 

It’s not widely known, but you can use different aggregation methods for ‘summing’ on non-numeric source line items.

 

Here is a solution (based on the L1 training model)

The use case here is that going forward the business wants to model by position rather than the individual employee, so they need to map the current employee data into the generic position.

 

In the source module (dimensioned by E2 Employee#) we have the following:

 

The required fields and their formats are:

• Name: Text
• Start Date: Date
• Current Salary: Number
• Promotion Review?: Boolean
• Review Period: Time (Months)

Note, the Position ID List line item is formatted as a list – Position ID.

 

The target module is dimensioned by position ID:

 

The blueprint is:

 And the formulae:

• Current Employee: 'SYS08 Employee Details'.Name[FIRSTNONBLANK: 'SYS08 Employee Details'.Position ID List]
• Start Date: 'SYS08 Employee Details'.Start Date[FIRSTNONBLANK: 'SYS08 Employee Details'.Position ID List]
• Current Salary: 'SYS08 Employee Details'.Current Salary[SUM: 'SYS08 Employee Details'.Position ID List]
• Promotion Review?: 'SYS08 Employee Details'.Promotion Review?[ANY: 'SYS08 Employee Details'.Position ID List]
• Review Period: 'SYS08 Employee Details'.Review Period[FIRSTNONBLANK: 'SYS08 Employee Details'.Position ID List]

Obviously, you do need to be careful with FIRST and LASTNONBLANK if there are duplicates that you are effectively trying to “sum”, but depending on the use case, these, and the other methods are a very powerful way to achieve a simple solution to the problem.

 

See here for more details of the different options

https://help.anaplan.com/anapedia/Content/Calculation_Functions/CF_Aggregation_Functions.html

 

I hope this helps

 

David