Copy Measures across hierarchies member using Advanced Formulas (Data Action)

[savik]

Hello SAP experts, 

I'm new to Data Action in SAC Planning, and in these days I faced a requirement that can't figure it out.

The goal: Copy a the value of two measures (let's say VALUE and PERCENTAGE) whose are in the combination of specified dimensions.

Dimensions in scope are:

• Account
• Product
• Plant
• Entity
• Owner
• Flow

This is an example that I can use as a starting point, from which I want to copy VALUE and PERCENTAGE:

ACCOUNT	PRODUCT	PLANT	ENTITY	OWNER	FLOW	VALUE	PERCENTAGE
C010101	TEST	PP01	ENT01	SPA	#	100,00	1,2
C010102	TEST-2	PP01	ENT02	FRA	#	4600.00	8.00
C010102	TEST	PP01	ENT02	FRA	#	1200.00	3.00
C010103	TEST	PP03	ENT03	POR	#	560.00	1.40
C010101	TEST	PP01	ENT01	SPA	01.01	0.00	0.00
C010101	TEST	PP01	ENT02	FRA	65.01	0.00	0.00
C010101	TEST	PP01	ENT01	SPA	55.01	0.00	0.00
C010102	TEST-2	PP01	ENT02	FRA	55.02	0.00	0.00
C010102	TEST	PP01	ENT02	FRA	55.02	0.00	0.00
C010103	TEST	PP03	ENT03	POR	48.01	0.00	0.00
C010103	TEST	PP03	ENT03	POR	66.01	0.00	0.00
C010103	TEST	PP03	ENT03	POR	77.01	0.00	0.00
…	…	…	…	…	…	…	…

And this is the result I'd like to obtain:

ACCOUNT	PRODUCT	PLANT	ENTITY	OWNER	FLOW	VALUE	PERCENTAGE
C010101	TEST	PP01	ENT01	SPA	#	100,00	1,2
C010102	TEST-2	PP01	ENT02	FRA	#	4600	8
C010102	TEST	PP01	ENT02	FRA	#	1200	3
C010103	TEST	PP03	ENT03	POR	#	560	1.4
C010101	TEST	PP01	ENT01	SPA	01.01	100,00	1,2
C010101	TEST	PP01	ENT02	FRA	65.01	1200.00	3.00
C010101	TEST	PP01	ENT01	SPA	55.01	100,00	1,2
C010102	TEST-2	PP01	ENT02	FRA	55.02	4600.00	8.00
C010102	TEST	PP01	ENT02	FRA	55.02	1200.00	3.00
C010103	TEST	PP03	ENT03	POR	48.01	560.00	1.40
C010103	TEST	PP03	ENT03	POR	66.01	560.00	1.40
C010103	TEST	PP03	ENT03	POR	77.01	560.00	1.40
…	…	…	…	…	…

So I basically need to copy measures from a Flow = # to other Flow "valorized" with != # in the combination (as shown in the table above).

I've tried many approaches, this is my last attempt (with less dimensions in the combo):

MEMBERSET [d/Account] = ("C010101", "C010102", "C010103", "C010104")

VARIABLEMEMBER #Account OF [d/Account]
VARIABLEMEMBER #Flow OF [d/Flow]
VARIABLEMEMBER #Owner OF [d/Owner]
VARIABLEMEMBER #Entity OF [d/Entity]

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

// In my attempts i tried also with FOREACH but i guess it's not necessary
IF RESULTLOOKUP([d/Flow] = #Flow, [d/Measures] = "VALUE") != NULL THEN // tried also with != 0 and 1-1

DATA([d/Flow] = #Flow,
     [d/Entity] = #Entity,
     [d/Owner] = #Owner,
     [d/Measures] = "VALUE") = RESULTLOOKUP( [d/Flow] = "#",
                                             [d/Entity] = #Entity,
                                             [d/Owner] = #Owner,
                                             [d/Measures] = "VALUE")
ENDIF

IF RESULTLOOKUP([d/Flow] = #Flow, [d/Measures] = "PERCENTAGE") != NULL THEN

DATA([d/Measures] = "PERCENTAGE") = RESULTLOOKUP(
                                                 [d/Flow] = "#",
                                                 [d/Entity] = #Entity,
                                                 [d/Owner] = #Owner,
                                                 [d/Measures] = "PERCENTAGE")

ENDIF

I can write value but to all Flows, not only to the "scoped" ones.

Any guess? I'll appreciate any help.

Thank you very much.

Edits:

# 1 - Extended source and target samples 
# 2 - Fixed target table values

[MoonJun]

Hi @savik 

I have a few questions about your requirements to make clear my understanding.

First,

Does the Source scope(blue square) data should be copied to 2 RED squares? It seems that the source data is not copied to the First Red square in your expected result.

Second, the measure "Value" is not copied to the Target scope in your script, but the fact data of the measure "Value" is copied on your expected result grid.

Regards,

Moonjun

[savik]

Hi @MoonJun, thank you for the reply. 

1. Yes,  data should be copied into the red squares as in the example (my bad, i fixed in the original post)

2. I'm not sure i understood, sorry. Would you mind to show me more specifically the point?

I want to give more info, trying to be more precise\clear and summarize the logic.

The formula must "iterate" every row for the combination of dimensions with Flow = "#",  then copy the measures values into the same combination but with Flow NOT EQUAL to "#"  - which are dynamic, different for every combination of Account-Product-Entity-Owner. 

Thanks again for your help.

[MoonJun]

Hi @savik

Please refer to the script below to get your expected result.

In my point of view, the Account member is not mapped to the Target scope, so the VARIABLEMEMBER script is added to ignore Account member dimensionality.

MEMBERSET [d/Measures] = "PERCENTAGE"
MEMBERSET [d/Account] = ("C010101", "C010102", "C010103", "C010104")
MEMBERSET [d/FLOW] != "#"

VARIABLEMEMBER #Account OF [d/Account]
DATA([d/Account] = #Account, [d/FLOW] = "#") = RESULTLOOKUP([d/FLOW] = "#")

IF RESULTLOOKUP() != Null THEN
	DATA() = RESULTLOOKUP([d/Account] = #Account, [d/FLOW] = "#")
ENDIF

I hope this is helpful to you, and if I have misunderstood anything, please feel free to reach out.

Moonjun