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

Highlighted
Contributor

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

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?

 

3 REPLIES 3
Highlighted
Certified Master Anaplanner

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

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 

Highlighted
Contributor

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

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. 

 

Highlighted
Certified Master Anaplanner

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

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 B).

 

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.