Hi Experts,


I have a requirement and i am confusing how to resolve this.


First Module    Row Axis:Product    Column Axis:Line Items

Second Module Row Axis:Flat Product  Column Axis:Line Items


Both Product and Flat Product have same members.


I am trying to multiply First Module line item * Second Module line item.


How to write formula different list items in different modules have same members.


i am trying to write formula by using lookup,Select,Sum function. but I am not able acheive the solution.


Kindly help on this.




Best Answer

  • akhil.kohli
    Answer ✓

    Hi Mahesh,


    This is a typical case of lookup.

    In your case, if its a normal list and there is a one-one relation, then you need to  get a reference of the other list in your respective dimension. So in your instance, in the product flat list, you can get a reference of product. Following are the steps how you do it


    1. In the module with Product Flat List dimension, make a line item (best practice say that instead of making a property, put it in a module with that dimension) with Product Format and write a formula in 'finditem(product, name(item(product flat list)))'. You will get the mapping of product against the product flat list. Below screenshot shows the same




    2. Now you can easily write a formula as first module.line item[LOOKUP:Product] * second module.line item.


    In case its a numbered list, you can do this with the code of the numbered list.


    Hope this helps





  • Hello,


    Create a property (type List) on your Product Flat list corresponding to your Product list.

    In your module dimensioned by Product Flat, retrieve the property in a line item.

    Then you can use the LOOKUP function to get the value coming from the module dimensioned by Product in this module.

  • This is a typical way, but not per the good practice. Best practices say that you need to maintain the properties of a dimension in your modules. One list properties increases the size in GBs because Each list item carries a memory allocation of 500 bytes per item and properties are additional to this, two it is not per Systems of PLANS way to do a module design.


    Read the below article







  • Thanks Akhil for insisting on best practices and sharing relevant document.