Using Lookup with offset or lag function

[mohitveer_sandhu]

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.

[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?

[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.

[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. 

1. Identify the dimensions of the Source Module. These are the values you have to pass in your LOOKUP formula ==> for example dimension A
2.  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

1. First, identifying what you need to do from a 'business" perspective - For Example, I need to retrieve the Product size to my Target module.
2. Identify which module has the size data line item you are trying to retrieve
3. 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 B)

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 · Accepted solution]

@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.