group by line item

AshaPatil
AshaPatil
in Modeling

Hi,

 

I have two different modules using two different dimensions.

module 1

module 2

In module 2  I have an amount value, which I want to pull in to module 1.

similarity between two module is Line item A (repeating text value), while pulling in to module 1 ,I want to group in amount value by Line item A( common line item in both the module).

Tagged:

Best Answer

  • bram.kurstjens
    bram.kurstjens
    Answer ✓

    Hi AshaPatil,

     

    You probably want to create a list (lets call it "List A") based on the text line item A and change the dimension of module 1 to List A. Then change the format of line item A to List A.

     

    In module 1 you can do:

    'Module 2'.Value[SUM: 'Module 2'.Line Item A]

     

     

    Edit: If you do not want to change to dimension of module 1, then you could also change the line item of module 1 line item A to format List A. The formula would then become:

    'Module 2'.Value[LOOKUP: 'Module 1'.Line item A, SUM: 'Module 2'.Line Item A]

     

     

    Hope this helps,

     

    Bram Kurstjens

Answers

  • Misbah
    Misbah

    @AshaPatil 

     

    When we have to aggregate the data we use SUM function with caveats that

    1. SUM to be applied on List formatted line item or list property

    2. That Particular list needs to be dimension of the target module - where you want to pull the data in.

     

    Or Else there is another way of doing it if you just want to aggregate it in same module you have to add LOOKUP function along with SUM Function

    Formula be like : Amount[SUM: List Formatted Line item, LOOKUP: List Formatted Line item]

    Please be aware that there are certain performance issues with SUM & LOOKUP being used together - there is an workaround that Anaplan has suggested - REFER SUM&LOOKUP Function in Anapedia

     

    Happy Planning!

    Thanks,

    Miz

  • rob_marshall
    rob_marshall

    @AshaPatil ,

     

    Please don't use Sum and Lookup together in the same expression as this could lead to performance issues (Planual rule 2.02-08), break them up into different line items/modules.  From what I can tell from the above (pictures would be nice to know exactly what you are trying to achieve), you will need to convert the text string into a list formatted line item and then you can do a sum off of that line item as Bram mentions in the first example.

     

    Hope this helps,

    Rob

Categories