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