Summing Values across dimensions

Mataylor
Mataylor
in Modeling

Hello -

 

I have modules dimensionalized by an L3 hierarchy (Co/Dep/Sub) with the line items equaling Accounts that are "collected" through a LIS.

 

Now I want to sum my values into an L4; which is just "L3" and "Account "strung together; but my formula of Sum - Lookup: the "L3" and "Account" attributes (Mapped to the L4) does not work.

 

Is there something I am missing here?

Tagged:

Answers

  • M.Kierepka
    M.Kierepka

    Hi @Mataylor,

    Can you share some screenshot of blueprints/lists/structures?

    If I understand correctly, you created join list "L4", which is L3xAccount (probably only for valid intersections), with parent as L3. Your source module has dimensions L3 and Account, and target module is just L4. So then you have two options for bringing data from source to target:

    1. LOOKUPs: in the SYS module for your L4 list you need to have 2 line items with format set as L3 and Account, that are either populated from code or during intersection creation. Then in your target module you just have a formula like: Source.Value[LOOKUP: 'SYS L4'.'L3, LOOKUP: 'SYS L4'.Account]
    2. SUM: in the mapping module dimensioned by L3 and Account (which you probably use for intersection creation) you should have line item formatted by L4. Then, in your target module, the formula would look like: Source.Value[SUM: 'SYS Creation L4'.'L4']
  • jagadishdash
    jagadishdash

    @Mataylor 

    You have the data in L3 hierarchy, And you want to bring to L4. So for that, if L3 module have account as well as L3.  In L4 you need to have account as dimension. and bring Account and L3 in the line item for mapping. 

    then Lookup with the Both Account and L3. 

    If you want for the same then do that line items in source module for mapping that's it.

     

    For a Glimpse, Refer to Level2: TRA01 module which has P2 Products  and shipping method as dimension 

    jagadishdash_0-1663910954266.png

    In target INV01 module have the dimensions as P3 SKU,  So the Lookup  is used with P2 as well as Shipping method.

    jagadishdash_1-1663911039394.png

    (For sum,You need to map in source module.)

     

     

    Hope you got it. If it is helpful for you, Please accept my answer as solution.

     

    Thanks,

    Jagadish Dash

     

Categories