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.
Best Answer
-
@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.
0
Comments
-
0
-
I tried but it gives invalid formula error0
-
@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?
0 -
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.
0 -
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.0 -
@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 190 -
@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.
0 -
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 .....
0 -
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
0 -
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 invalid0 -
Isn't there any solution without creating any extra line item.
0 -
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.
0
