sap analytics cloud data action advanced formula to distribute amount based on 4, 4, 5 week period

[hsthind]

We have a planning model in SAP Analytics Cloud with the following dimensions and measure:
Dimensions:
Cost Center: Represents the cost center (e.g., CC01).
GL Account: Represents the general ledger account (e.g., GL01).
Version: Represents the version of the plan (e.g., Budget).
Month: Represents the month in YYYYMM format (e.g., 202401
for January 2024).
Measure:
Amount: Represents the monthly planned amount.
Example Input Data
The initial data in the planning model is structured as follows, where each month has an equal amount of 100:

CostCenter GLAccount Version Month Amount
CC01 GL01 Budget 202401 100
CC01 GL01 Budget 202402 100
CC01 GL01 Budget 202403 100
CC01 GL01 Budget 202404 100
CC01 GL01 Budget 202405 100
CC01 GL01 Budget 202406 100
CC01 GL01 Budget 202407 100
CC01 GL01 Budget 202408 100
CC01 GL01 Budget 202409 100
CC01 GL01 Budget 202410 100
CC01 GL01 Budget 202411 100
CC01 GL01 Budget 202412 100

The total amount for the year is 1200 (100 per month × 12 months).
Requirement:
We need to redistribute the total yearly amount (1200) across months based on a 4-4-5 weekly pattern. Here's the distribution pattern:
January (202401) = 4 weeks
February (202402) = 4 weeks
March (202403) = 5 weeks
April (202404) = 4 weeks
May (202405) = 4 weeks
June (202406) = 5 weeks
July (202407) = 4 weeks
August (202408) = 4 weeks
September (202409) = 5 weeks
October (202410) = 4 weeks
November (202411) = 4 weeks
December (202412) = 5 weeks
Expected Output Data
After redistributing the amounts based on the 4-4-5 pattern, the data should look like this:

CostCenter GLAccount Version Month Amount
CC01 GL01 Budget 202401 92.31
CC01 GL01 Budget 202402 92.31
CC01 GL01 Budget 202403 115.38
CC01 GL01 Budget 202404 92.31
CC01 GL01 Budget 202405 92.31
CC01 GL01 Budget 202406 115.38
CC01 GL01 Budget 202407 92.31
CC01 GL01 Budget 202408 92.31
CC01 GL01 Budget 202409 115.38
CC01 GL01 Budget 202410 92.31
CC01 GL01 Budget 202411 92.31
CC01 GL01 Budget 202412 115.38

Key Details:
The total amount for the year remains the same (1200).
The monthly amount is redistributed based on the number of weeks in each month as per the 4-4-5 pattern.
Ask:
Please provide a Data Action script to achieve this redistribution in the SAP Analytics Cloud planning model. The script should calculate the redistributed amounts for each month based on the weekly distribution logic and write back the results to the model.

[MoonJun · Accepted solution]

Hi @hsthind 

If the date dimension granularity is Month, please try the data action script below to get your expected result.

MEMBERSET [d/Measures] = "Amount"
MEMBERSET [d/CostCenter] = "CC01"
MEMBERSET [d/Date] = "202401" TO "202412"
MEMBERSET [d/GLAccount] = "GL01"

VARIABLEMEMBER #NO_Week OF [d/GLAccount]
FLOAT @Sum_OF_Value

IF MOD(MONTH([d/Date]), 3) = 0 THEN
	DATA([d/GLAccount] = #NO_Week, [d/CostCenter] = "#") = 5
ELSE
	DATA([d/GLAccount] = #NO_Week, [d/CostCenter] = "#") = 4
ENDIF

@Sum_OF_Value = RESULTLOOKUP()
DATA() = @Sum_OF_Value * RESULTLOOKUP([d/GLAccount] = #NO_Week, [d/CostCenter] = "#") / 52

On my testing, I got the following result: 

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

Moonjun

[hsthind · Accepted solution]

Hello Moonjun, Many thanks for your quick response. Script work as expected, if it is just having one cost center. In the real data set we have more than 100 cost centers. What needs to be done to run it for each cost center, instead of fixing memberset for one cost center.

[MoonJun · Accepted solution]

Hi @hsthind

A simple solution is to wrap it with "FOREACH [d/CostCenter]" for a 15 and 16-line script. However, this may cause performance degradation.

You can achieve the expected result for all cost centers using the script below.

MEMBERSET [d/Measures] = "Amount"
// MEMBERSET [d/CostCenter] = "CC01"
MEMBERSET [d/Date] = "202401" TO "202412"
MEMBERSET [d/GLAccount] = "GL01"

VARIABLEMEMBER #NO_Week OF [d/GLAccount]
VARIABLEMEMBER #Sum_OF_Valu OF [d/GLAccount]

IF MOD(MONTH([d/Date]), 3) = 0 THEN
	DATA([d/GLAccount] = #NO_Week, [d/CostCenter] = "#") = 5
ELSE
	DATA([d/GLAccount] = #NO_Week, [d/CostCenter] = "#") = 4
ENDIF

DATA([d/GLAccount] = #Sum_OF_Valu, [d/Date] = "202412") = RESULTLOOKUP()
DATA() = RESULTLOOKUP([d/GLAccount] = #Sum_OF_Valu, [d/Date] = "202412") * RESULTLOOKUP([d/GLAccount] = #NO_Week, [d/CostCenter] = "#") / 52

Regards,

Moonjun

[KiranMallela]

If you want make more dynamic for all dimension combinations, Better to try with Date settings. Please refer below screen shot.

[William_Yu1]

I would say you can directly do that in story by using the distribute menu of table, please check below example and you also try out from your side: