SUM on non-shared Dimensionality

MarkTurkenburg_*8288
MarkTurkenburg_*8288
in Modeling

Hi,

 

I have a Target module which is Dimensionalised by Line Items and Time and a Source module which is dimensionalised by a numbered list and 3 line items (1 is direct input and 2 are List Formatted (1 being Time, 1 being Line Item Sub-set of the Target module Line Items)

 

In my Target module I wish to return the Direct Input line item from the source module and SUM on the Time formatted Line Item and Line Item Sub Set. The problem being is the Line Item in the Target Module and the List Formatted Line Item in the Source module have effectively no relationship

 

Is there a SUM/SELECT formula whereby you SUM on a Line Item but SELECT which List Item to SUM on?

 

Target Module

Target ModuleTarget Module

 

Source Module

Source ModuleSource Module

 

Many thanks!

 

Tagged:

Best Answer

  • rob_marshall
    rob_marshall
    Answer ✓

    @MarkTurkenburg ,

     

    There is a SUM/Lookup combination, but that can lead to performance issues at scale, so I would not advise that.  What I would suggest is creating a calculation module which sums the data you are wanting (your Mgmt Allocation list by time) - the formula would be Source Module.'$(AUD)]'[sum:'Mgmt.Alloc', Sum: Month].  Once you have this data dimensionalized, then you can can create a SYS "lookup" module (not dimensionalized by any lists), where you have the same line items (Software Transactional Revenue, Software Platform Revenue, etc.) that is list formatted as Mgmt Allocation (your list).  So, your formula in the target module will be, Calc module.data[lookup: SYS Lookup.Software Transactional Revenue].

     

    Hope this helps,

     

    Rob

Answers

Categories