cancel
Showing results for 
Search instead for 
Did you mean: 

Perform ResultLookup() using dynamic dimension members

former_member537307
Discoverer

I am in the middle of a planning project and we have the requirement where we need to look up a value from another line item based on the dimension members of the current line item.

E.g

So for plant A, product A, operation 2 we want to look up the value at plant A, product A, operation 1. I know this can be done with attributes, however, it can be the case that operation 2 could have a different previous operation, so we can not hard code this, it needs to be dynamic. How can this be done in advanced calculations?

Accepted Solutions (0)

Answers (1)

Answers (1)

N1kh1l
Active Contributor
0 Kudos

powrie

powrieFor looking up value there has to be some relationship between operations. How do you determine which is the previous operation ? As you said the operation 2 may need not have previous operation as 1 but rather 3. Does the previous operation determined by time factor ? You have not shown the time dimension in your example. You can try creating a FOREACH with Product and Operations but again in lack of what determines previous operation its difficult to provide a answer.

Hope this helps !!

Nikhil

former_member537307
Discoverer

Hi Nikhil,

The previous operation will be a member in another dimension called "previous operation" as shown in the example, this comes from SAP/when we book the data. Time is not a factor to determine it.

it will only be run on booked values so I go:

FOREACH.BOOKED [PLANT], [PRODUCT], [OPERATION]

DATA([MEASURES] = "VALUE") = RESULTLOOKUP([MEASURES] = "VALUE", [OPERATION] = [PREVIOUS OPERATION])

ENFOR

Obviously this doesn't work because the result lookup is looking for records were "operation" and "previous operation" are equal, and there are none. So I am looking for a way to make that result lookup look for records where the "operation" is the same as the value of "previous operation" for the "operation" in the current foreach loop.

E.g in the example we look at "Plant A", "Product" A, and "Operation" 1. The "Previous operation" is # so the code will give a default value (this will come from another measure in reality but we can default to 1 for simplicity). Now for "Operation" 2 the "Previous operation" is 1, so the resultlookup should look for the value at "Operation" 1 which would be 1, but now we would multiply it by a yield, say 0.8 (so the value at "Operation" 2 is now 0.8). Now we go to "Operation" 3, the "Previous operation" is 2, so the look up should get the value we just calculated (0.8), and again multiply by a yield, lets say 0.8 again, giving us 0.64. The yield is related to the value we are looking up, so I cant just remember the previous value in the look using a variable as I still need to lookup the yields.

I hope this clarifies whats needed.

Kind regards,

Ed