Using Lookup with offset or lag function

Hi,
I have a module (MOD01) with time and 'A' as dimensions and a line item x.

In another module (MOD02) I want to have a value of say in JUN 20 I need value of x from JAN 19 based on the Dimension 'A'(by lookup).

So which function should I use or how can I use lookup with time period function.

Accepted answers

einas.ibrahim

@mohitveer_sandhu

Try breaking down the formula.

First get the lookup value based on A-B mapping. The result will be in the same time period as MOD01. Then Offset the results by the offset value in MOD02. That should work.

All comments

kavinkumar

@mohitveer_sandhu

I hope this should work!

OFFSET(MOD01.x[LOOKUP:'LIST A'],-12,0)

mohitveer_sandhu

I tried but it gives invalid formula error

einas.ibrahim

@mohitveer_sandhu

Can you please clarify what do you mean by "based on the Dimension 'A'"?

Are you trying to offset the line item values from one year to another? for example Jan 19 ==> Jan 20 and Feb 19 ==> Feb 20. Or do you have a different logic?

kavinkumar

Ideally, it should work and it does work for me. Can you post the error message after writing the formula?

Maybe another way to achieve this is by creating 2 line items. One to bring the value as is through lookup function. Then in the second line item apply Offset/Lag on the first line item.

I hope this helps.

mohitveer_sandhu

@einas.ibrahim

Based on Dimension A means using lookup on Dimension A.
Let's say the dimension values used in the MOD02 are mapped to Dimension A items say C1 is mapped to B1 and C2 is Mapped to B2 and C3 is mapped to B3.
So in MOD02 I want to use the value in the current period i.e., JUN20 the value of line item MOD01.x for period JAN19 but if the dimension in MOD02 is C1 it should take the Value from MOD01.x for period JAN19 for B1 and so on.

And It is not the previous year's value. It could be for any period. Jan19 ==> JUN20 or Jan19 ==> OCT20.

mohitveer_sandhu

@kavinkumar Here is the error message

The formula for 'MOD02'.test is invalid:

'MOD02'.test = OFFSET('MOD01'.x[LOOKUP: A], offset_value, 0)
Offset value has -ve number's based on the difference between JUN20 and JAN 19

einas.ibrahim

@mohitveer_sandhu

Maybe because I'm a visual person, I use a very systematic way to figure out the construct of my LOOKUPs.

Identify the dimensions of the Source Module. These are the values you have to pass in your LOOKUP formula ==> for example dimension A
Review the dimensions available to you in the Target module.
- If you have dimension A in the Target, then you don't need a lookup (that's obviously not your case)
- If you have a different dimension in the Target module, such as B, then you have to look for a mapping module that is dimensioned by B (same as Target module) and have a list formatted line item of type list A.

I try to provide an unbroken link between my Source and Target modules where the output (or return value) of one module serves as the input to the next module in the link until I reached my final Target module.

You are probably explaining your issue fine, but I am not able to visualize it. If you break down the problem by

First, identifying what you need to do from a 'business" perspective - For Example, I need to retrieve the Product size to my Target module.
Identify which module has the size data line item you are trying to retrieve
Then start constructing your LOOKUP link

You obviously have the complication of the different Time value between your source and target. How are you determining if Jan 19 should go to Jan 20 or Oct 20 for example? If you are using a mapping system module then you can do a LOOKUP as well.

mohitveer_sandhu

@einas.ibrahim

The determining factor of whether jan19 data goes to jun20 or oct20 depends on when the entity in List A joins.
I have the mapping module and offset value. The Only issue is I am unable to look up and offset at the same time.

For your visualization

MOD01 => Page selector => Dimension B(B1)

Line Item\Time Jan-19 Feb-19 Mar-19

x 100 200 300

MOD01 => Page selector => Dimension B(B2)

Line Item\Time Jan-19 Feb-19 Mar-19

x 400 500 600

Mapping module =>

A/Line Item map_to(Format: List

C1 B1

C2 B2

MOD02 => Page selector => Dimension A->C1

Line Item\Time Jun-20 Jul-20 Aug-20 .....

mapped_to B1 B1 B1 .....

required result 100 200 300 .....

MOD02 => Page selector => Dimension A->C2

Line Item\Time Oct-20 Nov-20 Dec-20 .....

mapped_to B2 B2 B2 .....

required result 400 500 600 .....

einas.ibrahim

@mohitveer_sandhu

Where do you have the logic for "when the entity in List A joins" ?

Because let's say you have that in a mapping module like the one for A and B, then you can add the mapping to the LOOKUP formula.

MOD01.x = MOD02[LOOKUP:Mapping module.map_to, LOOKUP: Period Mapping]

if you have the logic for the period differently from my assumption, let me know and we can find a different solution

mohitveer_sandhu

I have the offset value in mod02 which gives the difference of period in Mod02 and Mod01
i.e., for Jun20 offset = 17
now I need something like
Mod02.required result = offset(mod01.x[lookup:'Mapping module'.map_to], offset_value, offset_value+1)

But this formula returns an error as invalid

einas.ibrahim

@mohitveer_sandhu

Try breaking down the formula.

First get the lookup value based on A-B mapping. The result will be in the same time period as MOD01. Then Offset the results by the offset value in MOD02. That should work.

mohitveer_sandhu

@einas.ibrahim

Isn't there any solution without creating any extra line item.

einas.ibrahim

@mohitveer_sandhu

Not that I can think of.

a lot of Anaplan formulas don’t work in a nested way. Also, it’s best practice to breakdown formulas anyway.

Quick Links

Can you help?

Unanswered questions

Using Anaplan for Managing Custom Product Configurations (Example: Designer Dolls)
We’ve recently started using Anaplan to model and manage our supply chain and production planning for a niche ecommerce business that sells custom-designed dolls — each made-to-order with specific features, materials, and delivery lead times. A few interesting challenges we faced: SKU Explosion: Each doll can be customized…
OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…