SAC Planning Advanced formula code clarification.

[praveen_kumar334]

Hi Team,

Can someone please help me in understanding the below piece of code. 

DATA([d/Measures] = "ABC") 
	= RESULTLOOKUP([d/Measures] = "M1", [d/Version] = "VERSION1", [d/Date] = "202101", [d/DIM2] = "#")
	* (RESULTLOOKUP([d/Measures] = "M2", [d/Version] = "VERSION1", [d/Date] = "202101", [d/D3] = "#", [d/D4] = "#",[d/MATERIAL] = "#", [d/t.S:SAP_ALL_COSTCENTER] = "#", [d/CCACTIVITYTYPE] = "#", [d/FIXVARIABLE] = "#", [d/COMPQUANTITY_UNIT] = "#") * 0 + 1)

Here I am confused with the last piece of code * 0 + 1. Can someone please help me on this.

[hartmut_koerner]

This formula takes measure M1, but only if there are data in measure M2.

Multiplikation makes a join, *0+1 sets the join partner to 1 so that the left side is multiplied with 1 - but only if there are data on the right side.

[ThomasK]

Hi, the general structure of the statement is

DATA([d/Measures] = "ABC")
= RESULTLOOKUP([d/Measures] = "M1")
* RESULTLOOKUP([d/Measures] = "M2")

This results in an inner-join that the measure ABC is calculated only in the case where values for M1 and M2 are existing.

The technique using 

(RESULTLOOKUP([d/Measures] = "M2") * 0 + 1)

is a common workaround to set all values where M2 is existing to 1. The multiplication is now M1 * 1 (where M2 exists). This results finally in keeping only M1 values where M2 exists, by using the inner-join behavior explained above. 

BR Thomas

(Thanks for Kudos)

[praveen_kumar334]

Thanks Thomas. I am still not cleared 😞 Can you please elaborate a bit more. Thanks for understanding.

[praveen_kumar334]

Hi Thomas , team,

 Below is my understanding. I have created one sample model to understand on this. 

Model Structure : 

Story : 

I have created small data action considering this scenario.

Here when we multiply the resultlookup value with 0 , then it doesn't make sense to have that script. Can you please help with this scenario. 

Thanks in advance. 

Regards,

Praveen.