Moving values from one module to another while Line item is being used as Dimension in other

Gpatwa
Gpatwa
in Modeling

Hi everyone:

 

Module 1: Dim - Entity (Aus 1, Aus 2....)  

                 LI - please refer below as (numbers)

Gpatwa_0-1666775916249.png

 

Module 2: Dimension: LI of first module , Parent of Entity (Aus..)

                 Line item: Amount (which will have the values from the first module)

Gpatwa_1-1666776257068.png

I am getting same value distributed across all the Dim 2. 

Can someone help me out in this case how we going to move the values from module 1 to module 2.

 

Thanks

 

 

Tagged:

Best Answer

  • Misbah
    Misbah
    Answer ✓

    @Gpatwa 

    Hey, module 2 is based off of LIne item Subset (LIS) and not line items. So you will have to use COLLECT function first so that use LOOKUP. My recommendation is to  Introduce TIME dimension in Module 2 . Steps to follow:

     

    Module 2 dimensioned by LIS, Parent of Dimension 1, TIME and one line item. (Do not use FY22 name in line item, just say Totals). Use COLLECT() as formula

    Create SYS Global module with no dimension. Create a line item call it as Time and format it as Time-Year. Manually pick FY22 in this line

    Create Module 3 dimensioned by LIS, Parent of Dimension 1 and one line item. Use formula as Module 2.Totals[LOOKUP:SYS Global module. Time].

    We do not use SELECT over time. That is hardcoding and messes up with scalability of the model.

    Hope that helps,

    Misbah

    Miz Logix

Comments

  • Misbah
    Misbah

    @Gpatwa 

     

    Could you share the formula that you have used in the second module? I think you have missed to tackle TIME dimension in your formula.

     

    Also, confirm the dimensionality/artifacts of you modules

    Module 1: Dimension 1, Time in Years and Line items

    Module 2: Parent of Dimension 1, Line item Subset (based on Module 1) and One Line item

     

    Thanks,

    Misbah

    Miz Logix

  • Gpatwa
    Gpatwa
    Hi @Misbah,

    Thank for your quick response,
    As you mentioned about time, i have used time(Year) in module 1 and have not used in module 2 (instead i have referred it).

    Here is the structure for both modules.
    Module 1: Dimension 1, Time in Years and Line items
    Module 2: Parent of Dimension 1, Line item (based on Module 1) and One Line item (this will have same values from module 1)

    Formulas used in the module 2 LI: 'ESG DAT01 - TTC Data Module'.Corporate income[SELECT: TIME.'FY22']

    Let me know if you need any more info.

    Thanks,
    Gaurav

Categories