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

HeidiKing
HeidiKing
in Modeling

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

Tagged:

Best Answer

  • DavidSmith
    DavidSmith
    Answer ✓

    @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:

    2019-11-14_10-56-26.png

     

    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:

    2019-11-14_11-38-48.png

     

    The blueprint is:

    2019-11-14_11-39-54.png

     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

Answers

  • HeidiKing
    HeidiKing

    **** thank you so much David this is amazing! 

     

    This will solve so many headaches, thanks for the very comprehensive reply.

  • DavidSmith
    DavidSmith

    I would add that TEXTLIST is also an option.  It's not applicable for this example.

     

    But as we know TEXTLIST is a very heavy calc and should be avoided if possible (as per Planual 2.02-11).  If you do need to use it, be careful and ensure there is no other way to solve the problem, and make sure the target dimensions are appropriate to reduce the target calculation size

    David

Categories