Method to Reverse the Derivation from Dimension Property

[bautayakool]

Hello experts,

I have an inquiry about dimension ID derivation by referring to the property. As normally, we can derive a dimension member by looking at the property.

For example, we have 2 dimensions: product dimension and product group dimension. Product dimension contains product group property which can be used to derive member ID in product group dimension, as shown below.

Given the dimension properties, if a value is inputted in product dimension, we can derive product group using data action by using:

"data([d/product_group] = [d/product].[p/product_group]) = resultlookup()"

What if the case is reversed? Is there a way to derive product dimension when value is inputted in product group dimension? For example, product group A got a sales amount of $10. Is there a way to look into product dimension to map product dimension's property to product group ID to get the product dimension id?

Thank you.

Bhumirapee Autayakool.

[N1kh1l · Accepted solution]

@bautayakool 

Try the below

FOREACH [d/costcentre],[d/productline]

IF [d/costcentre]=[d/productline].[p/costcentre] THEN

DATA()=RESULTLOOKUP([d/productline]="#")

ENDIF

[bautayakool · Accepted solution]

It worked! Thank you so much for your guidance!

[N1kh1l]

@bautayakool 

Product Group to Product is N:1 mapping. Not sure what is the use case but you can try FOREACH [d/product].[p/productgroup]

This will group products with similar product group values.

Nikhil

[bautayakool]

Right now, we encountered a situation where we have the information in Cost Center dimension, which we would like to be mapped back to Production Line dimension.

The cost center can be mapped based on the production line dimension property. Both of ways are 1:1 mapping. Like so:

[N1kh1l]

can you show a sample example with what you want to do? Map the costcentres to product lines

DATA([d/costcentre]=[d/productline].[p/costcentre])

[bautayakool]

@N1kh1l 

Specifically, we would like to achieve this result. We have a total headcount information in Cost Center level. We would like to map the values to production line. As of right now, the mapping only exists in production line dimension, and there is a message from client to not put the mapping in the cost center dimension.