Formula

UMAMAHESH

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.

Regards,

Mahesh

Accepted answers

akhil.kohli

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

Regards,

Akhil

All comments

akhil.kohli

Hi Mahesh,

This is a typical case of lookup.

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

Regards,

Akhil

g.ganivet

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.

akhil.kohli

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

https://community.anaplan.com/t5/Knowledge/Best-Practices-for-Module-Design/ta-p/35993

Thanks

Akhil

hari.prasad

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

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…