Summing Module Results at Different Levels in Hierarchy

PrevContributor
PrevContributor
in Modeling

I'm relatively new to Anaplan having just completed the various training courses.  I've been trying to figure this out for a couple of days using sum and lookup but seem to be encountering the same error "level mismatch".  Here's my issue... I have a module that uses a numbered list as a dimension having three levels: Organization -> L1 -> L2 In the module I've calculated a line item value at that L2 level The combination of Org and L1 is my "parent hierarchy" list.  L2 data is equivalent to data in a separate list.  This other list is the dimension I'm trying to use in my new module attempting to aggregate the results of the first module as shown below. My objective is to create a secondary module that references the total amount at the L2 dimension value but sums up all of the values for the same L2. so, I have: Org     L1     L2     value 1         x       a        100 1         y       a        200 1         x       b        100 1         z       b        300 What I want to be able to produce is: L2     value a       300 b       400 as indicated, the 'a' value in the second module is from a different list where the values in the list match the values in the L2 component of the originating module. As I said, I'm new to this whole game and I don't get much help from the very generic descriptions/examples in the help so I was hoping to turn to some more experienced users for some help using functions or combining functions to get this result

Tagged:

Best Answer

  • alec_judd
    alec_judd
    Answer ✓
    Hi Steven,

    I believe this is what you're looking for.  I set up 4 lists (Org -> #L1 -> #L2 and L2).  I used a FINDITEM property in the #L2 list to find the correct list item in the non-numbered L2 list and did a SUM on that.  Using a FINDITEM function in a list property will allow you to tie the two lists together by allowing the A in the numbered list to match up with the A in the non-numbered list.

    LISTS:
    Organization (Non-numbered, parent of L1), Items: "1"
    #L1 (Numbered, parent of L2), Items: "X,Y,X,Z"
    #L2 (Numbered), Items: "A,A,B,B"
    L2 (Non-numbered) (Basic List of two items, "A, B")

    STEPS:
    1. Added codes to the #L2 list (A1, A2, B1, B2)
    2. Added a property to the #L2 List labeled "FINDITEM L2", formatted as the L2 list, with formula: FINDITEM('L2', LEFT(CODE(ITEM('#L2')), 1))
           -You need to be able to Sum on this FINDITEM property in your new module.  By doing the LEFT(CODE()), etc. you are able to use the letter of that code to match to the original item in the L2 list.
    3. Added a new module "New Aggregation" with dimensions of the L2 list and one line item called "Values".  My original (1st) module where the data is stored is called '#L2 Values'.
    4. I added this formula for "Values":
    '#L2 Values'.Value[SUM: '#L2'.'FINDITEM L2']

    That should give you the 300 and 400.  In the "New Aggregation" module, it is basically then summing up anywhere it recognizes an A item or a B item in the #L2 list of your first module.  I know this is a bit confusing trying to read through rather than seeing, but for whatever reason I wasn't able to post screenshots to this message.  If you need any help or if I misunderstood your example, just let me know.

    Thanks,
    Alec

Answers

Categories