SAC- Advanced Formulas - contribution for previous 2 years based on parameter yyyymm

[mayiya]

Hi experts

I need to calculate the contribution percentage based on a date parameter.

Example:

%Date% = 202304

The contribution must be calculated from first month of previous 2 years to %Date% :

Contribution would be for year: month/total year. ( for 202101 -> 10/150, for 202201 -> 30/400...)

I'm facing 2 problems:

1. I'm trying to setting the MEMBERSET with :

MEMBERSET [d/Date]= PREVIOUS (24, "MONTH", %Date%) to %Date%

and the result is 202104 to 202304. How can I set 202101 ? When using FIRST (PREVIOUS (24, "MONTH", %Date%)) the message "No viable alternative at input "FIRST".

I cannot concatenate on SAC, so I don't know how can I solve this. And using InitialDate on Version is not possible because I need to use private versions.

2. How can I calculate the total for each year? When using VARIABLES cannot define any PREVIOUS() function, for example VARIABLEMEMBER #Total1 OF PREVIOUS (2, "YEAR", %Date%) or @Total1= PREVIOUS (2, "YEAR", %Date%) shows message: "No viable alternative at input "FIRST"

Please help,

[N1kh1l · Accepted solution]

mayiya

mayiyaYou can try a workaround as shown below.

Instead of 24 look back 36 months. So If 2026111 is passed as date parameter you would need 202401 to 202611 as a scope. I think the max you would need is 35 periods if any Year November is passed as date selection. ( 11 periods CY, 12 CY-1, 12 CY-2). To avoid the 3rd year lookback you can check for the year difference between data parameter and lookback months and it should be not less than 3 (0 to 2). In below example my source data is in A1 and contribution calculation is in A2 and for simplicity I kept same values in each period of the year.

Please Note this approach is a workaround for 2-4 years lookback but for larger year lookback you should use Userdefined date dimension and maintain some attributes to help you with calculation.

Adjust the below as per your model and dimension names.

MEMBERSET [d/Date]= PREVIOUS (36, "MONTH", %Date%) to %Date%
MEMBERSET [d/Measures]="Amount"
MEMBERSET [d/Account]="A1"

VARIABLEMEMBER #Y0 OF [d/Date] // Current Year Total
VARIABLEMEMBER #Y1 OF [d/Date] // Current Year-1 Total
VARIABLEMEMBER #Y2 OF [d/Date] // Current Year-2 Total

INTEGER @DATEYEAR
@DATEYEAR=YEAR(%Date%)


	IF @DATEYEAR-YEAR([d/Date])=0 THEN
		DELETE([d/Account]="A2")
		DATA([d/Date]=#Y0)=RESULTLOOKUP()
		DATA([d/Account]="A2")=RESULTLOOKUP()/RESULTLOOKUP([d/Date]=#Y0)
	ELSEIF @DATEYEAR-YEAR([d/Date])=1  THEN
		DELETE([d/Account]="A2")
		DATA([d/Date]=#Y1)=RESULTLOOKUP()
		DATA([d/Account]="A2")=RESULTLOOKUP()/RESULTLOOKUP([d/Date]=#Y1)
	ELSEIF @DATEYEAR-YEAR([d/Date])=2  THEN
		DELETE([d/Account]="A2")
		DATA([d/Date]=#Y2)=RESULTLOOKUP()
		DATA([d/Account]="A2")=RESULTLOOKUP()/RESULTLOOKUP([d/Date]=#Y2) 
	ENDIF

Output: Please note intentionally took 2023 to show no impact on any period prior to CY-2 January. Also see no calculation for 202612 as 202611 was passed in parameter.

2024: 2023 no calculation

2025:

2026: No calculation for 202612 as parameter is 202612 and it is also not included in 2026 total.

Br.

Nikhil

[mayiya · Accepted solution]

Thanks Nikhil.

It worked!!. I'm searching how to improve performance, because it it taking too long , and I only have few records for testing.