SAC Foreach restricted by time

[Deb_D]

Hello, 

In my CAPEX project I have a parameter for the project life expected - for example 6 years that are inserted today.
On the other hand, I have the first year of investment set as "1" in the relevant year
I want to create a foreach calculation on a "Discount (life) Years" for further calculations
and my question is how to create the scope of 2024 to 2024+6 on my data action using the above inserted assumptions?

thanks
PS print screens 

Thank you 

Deb

[N1kh1l]

So you mean Discount Life Years for project = Year of First Investment (2024) + Useful life (6) so 2024 to 2029

[MoonJun]

Hi @Deb_D

I have another script to get your expected result. The main logic is the same as the previous script. 

The code is below

MEMBERSET [d/Date] = "2024" TO "2033"
MEMBERSET [d/Measures] = "Value"
MEMBERSET [d/Investments_Developments] = ("JM_EU", "JM_HQ")

INTEGER @First_Year
INTEGER @Project_Life

FOREACH [d/Investments_Developments]
	@Project_Life = RESULTLOOKUP([d/Account] = "Project_Life_in_Years")
	
	IF RESULTLOOKUP([d/Account] = "First_Investment_Year") = 1 THEN
		@First_Year = YEAR([d/Date])
	ENDIF

	IF YEAR([d/Date]) >= @First_Year THEN
		IF @Project_Life > (YEAR([d/Date]) - @First_Year) THEN
			DATA([d/Account] = "Discount_Life_Years") = YEAR([d/Date]) - @First_Year + 1
		ENDIF
	ENDIF
ENDFOR

Result of the script

I hope this helps you.

Moonjun

[N1kh1l · Accepted solution]

@Deb_D 

Interesting Modelling Design. 

Have tried some rough coding and its working in my monthly date model. My product dimension is like your Investment dimension.

So for first project FIY is 202401 and Project life is 6 so Discount life spreads from 202401 to 202406 and for second project FIY is 202402 and Project Life is 5 so discount Life spreads from 202402 to 202406

The code is below

MEMBERSET [d/Measures] = ("GROSS_SALES","RETAIL_RETURNS","QUANTITY") // Change this to your 3 accounts
MEMBERSET [d/Date] = "202401" TO "202412"
MEMBERSET [d/SAP_SMPL_PRODUCT]=("MZ-FG-P010","MZ-SR-RC002")
MEMBERSET [d/SAP_SMPL_CUSTOMER]="1000151"
MEMBERSET [d/SAP_SMPL_ENTITY]="ENT0001"
MEMBERSET [d/SAP_SMPL_SALESORG]="SO0001"

INTEGER @Iteration

VARIABLEMEMBER #Proj_life OF [d/Date]
VARIABLEMEMBER #FIY OF [d/Date]
DELETE([d/Measures]="RETAIL_RETURNS") // Change this to Account member Discount Life Years


DATA([d/Date]=#Proj_life,[d/Measures]="GROSS_SALES")=RESULTLOOKUP([d/Measures]="GROSS_SALES") //Change [d/Measures]="GROSS_SALES" to your Project Life Account member

FOREACH [d/SAP_SMPL_PRODUCT]  //-- Change this to your Investment dimension
@Iteration=1
IF RESULTLOOKUP([d/Measures]="QUANTITY") =1 THEN  //-- change [d/Measures]="QUANTITY") to Account First Invetsment Year
DATA([d/Date]=#FIY,[d/Measures]="QUANTITY")=MONTH([d/Date]) //-- Change 	MONTH([d/Date] to YEAR([d/Date] if granularity of date is Year
ENDIF

//--- Replace MONTH() to YEAR() and [d/Measures]="QUANTITY" to Account First Invetsment Year and [d/Measures]="GROSS_SALES" to Project Life Account member
	
	FOREACH [d/Date]
	IF MONTH([d/Date])>= RESULTLOOKUP([d/Date]=#FIY,[d/Measures]="QUANTITY") AND (MONTH([d/Date])-RESULTLOOKUP([d/Date]=#FIY,[d/Measures]="QUANTITY")<RESULTLOOKUP([d/Measures]="GROSS_SALES",[d/Date]=#Proj_life)) THEN

		DATA([d/Measures]="RETAIL_RETURNS")=@Iteration // Change [d/Measures]="RETAIL_RETURNS" to Account member Discount Life Years
		@Iteration = @Iteration + 1
	ENDIF
	ENDFOR
		
ENDFOR

Hope this helps !!

Nikhil

[Deb_D · Accepted solution]

@N1kh1l thank you

[thomas_beck]

Hi @Deb_D ,

You can also check the example on equipment depreciation using Advanced Formulas from the SAC Help: https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/e88b9a87633c4e19a0dbd...

It does use some parameters so can be easily adapted.

Regards,

Thomas

[Deb_D]

@thomas_beck Thank you

[N1kh1l]

@MoonJun 

I have always had hard time with variables as they are additive and do not get cleared when next record arrives. I had first written my code using variables but I think I was having issues with them getting cleared ( may be I made mistakes as I wrote in hurry). Could you try changing the project life of second project from first year to some other year ( different than 1st project) and see if your code still works.

Nikhil

[MoonJun]

Hi @N1kh1l 

The value of "project life" is assigned to the Variable "@project_life" which ignores the Date dimension member & other dimensionality also. So moving it to next year is working as our expectation. 

 

In addition, if there are 2 values for the Project Life, it is working on the Aggregated Project Life value. It is a case of the extended useful life for calculating depreciation expense.  Please refer below screenshot. 

The Data dimension Member(2024 or 2025...) affects only the starting "Discount(Life) years" which is the same as the Date dimension member(2025) of the "First Investment Year". 

Moonjun

[Deb_D]

Hey @MoonJun 

Looks good thank you!

[Deb_D]

yes - I just don't know how to write it @N1kh1l