Look up values in a module that stores data against a list

RoopaliB

Hi Anaplaners,

I am new to Anaplan. Please help!

Module A:

Lisy Y: List of years

Line Items:

LIA1

Format: Number

Value(Has a value stored for every year)

Module B:

Line Items:

LIB1

Format: List Y

Value 2018

LIB2

Format: Number

Value 2

LIB3

Format: Number

Formula: 'Module A'.'LIA1' [LOOKUP: (LIB1+'LIB2')]

I want to get the Value for a future year using this formula and it is not working becase LOOKUP requires a list. I am not sure how to accomodate the future yr in the LOOKUP formula.

Can you please tell me how to resolve this?

Accepted answers

Adithya S

Hi @RoopaliB,

I have a workaround for that and looking at the instructions you have mentioned, I have presumed that you are not using time as a dimension. Instead, you have used it in a list and later using the same again in module 'A' and as list formatted in (Module .

Now, there are certain steps that you have to do in order to achieve this workaround.

1) Create a sequence for the years in the list (as a property). Sequence should be 2018 = 1, 2019 = 2 etc...

2) Now create three new line items in MODULE B (LIB2A, LIB2B, LIB2C).

3) LIB2A = LIST Y.Sequence[LOOKUP: 'LIB1'] + 'LIB2'

4) LIB2B = IF 'LIB2A' = LIST Y.Sequence THEN ITEM(LIST Y) ELSE BLANK

5) LIB2C = LIB2B

If you were to use TIME as dimension then you can achieve this by using various functions mentioned in ANAPEDIA like OFFSET, LEAD etc. Since yours is list formatted, this is the workaround that I could think of.

I have attached the snapshots for your reference. If this helps and solves your question then please mark this as the solution.

FInal_Module_B_answer_31476.PNG

LIST_Y_GRID_VIEW_31476.PNG

LIST_Y_properties_31476.PNG

Module_A_31476.PNG

Module_B_31476.PNG

All comments

Adithya S

Hi @RoopaliB,

I recreated what you have mentioned in the instructions and this is what I got as a result. However, I am not sure why you would use year in a list format (I am presuming that it might be for some purpose). Anyway, here is the actual way that I have done and maybe you can check with yours.

The formula should be 'Module 1'.'LIA1'[LOOKUP: 'LIB1'] + 'LIB2'

Let me know if this helps

Thanks

Adithya

Module_A__and_LIA1_31420.PNG

Module_B_and_LIB__1,2,3_31420.PNG

Module_B_Blueprint_31420.PNG

RoopaliB

Hi Adithya,

Thank you creating the mock up.

What I am trying to achieve basically is the value of the line item LIA1 moved forward by 2. So in this case it will be the 5000 number from 2020.

So basically based on the value of LIB2 the formula should fetch the value of LIA1 from those many years forward.

Adithya S

Hi @RoopaliB,

Now, there are certain steps that you have to do in order to achieve this workaround.

1) Create a sequence for the years in the list (as a property). Sequence should be 2018 = 1, 2019 = 2 etc...

2) Now create three new line items in MODULE B (LIB2A, LIB2B, LIB2C).

3) LIB2A = LIST Y.Sequence[LOOKUP: 'LIB1'] + 'LIB2'

4) LIB2B = IF 'LIB2A' = LIST Y.Sequence THEN ITEM(LIST Y) ELSE BLANK

5) LIB2C = LIB2B

I have attached the snapshots for your reference. If this helps and solves your question then please mark this as the solution.

FInal_Module_B_answer_31476.PNG

LIST_Y_GRID_VIEW_31476.PNG

LIST_Y_properties_31476.PNG

Module_A_31476.PNG

Module_B_31476.PNG

Quick Links

Can you help?

Unanswered questions

ADO File Upload - Semicolon Delimiter
Hi, Based on the regional settings, my default delimiter/separator for CSV files is a Semicolon. In the classic way of uploading a csv file, I was able to select the delimiter. When using ADO, I don't have the option to select a delimiter. Uploading a Semicolon delimited file returns an error, or an upload with only one…
Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…