Distribution based on dynamic weights using #Variablemembers in SAC

[dionysis_evripidou]

I am trying to distribute some values using a data action, in this example of Plan 2023 from an unassigned member, to the members of Product A and Product B of Product Dimension. I want the distribution to be based on the weight of Products of the Actuals of 2023. For simplicity I differentiate the Actuals and Plan using a dimension called "value type" (Actuals have the value 010 and Plan the 020).

I tried applying the following code:

//Define the membersets

MEMBERSET [d/Product]=("Product A","Product B")

MEMBERSET [d/Date].[p/YEAR]="2023"

MEMBERSET [d/Company_Code]="Company A"

MEMBERSET [d/Value_Type]=("020")

//Define variables

VARIABLEMEMBER #Weight OF [d/Product]

VARIABLEMEMBER #SumProduct OF [d/Product]

// Step 1: Calculate Total Actuals for 2023

DATA([d/Product]=#SumProduct)=RESULTLOOKUP([d/Measures]="Measure",

   [d/Version]="public.Actual",

   [d/Value_Type]="010",

   [d/Date]="2023",

   [d/Company_Code]="Company A")

//Step 2: Calculate Distribution Weights

FOREACH [d/Product]

DATA([d/Measures]="Measure", [d/Product]=#Weight)=RESULTLOOKUP([d/Measures]="Measure",

   [d/Version]="public.Actual",

   [d/Value_Type]="010",

   [d/Date]="2023",

   [d/Company_Code]="Company A")

/ RESULTLOOKUP([d/Product]=#SumProduct)                                

ENDFOR

//Step 3: Distribute the values

DATA()=RESULTLOOKUP([d/Product]="#",

[d/Version]="public.Actual",

[d/Value_Type]="020",

[d/Date]="2023",

[d/Company_Code]="Company A")

* RESULTLOOKUP([d/Product]=#Weight)

//Step 4: Delete the data from "Unassigned" after the distribution

DATA([d/Date]="2023",[d/Product]="#",[d/Value_Type]="020")=0

Do you have any suggestions?

Thanks in advance.

[dionysis_evripidou]

@N1kh1l Thanks again for you valuable input. Now I am trying to apply the logic to a more complex model containing several dimensions and looks like this.

I also have several other dimensions but I did not include them in my meberset. Now the value type for Actuals is 010, and for Forecast is 061. The adapted code is the follwing:

//Functionality of the data action: Distributes the values of FC1 (PC+0) based on the weight of Actuals (PC-1)

//Define Membersets

MEMBERSET [d/PWC_DM_PLANCL].[p/Flag] = "PC+0"

MEMBERSET [d/PWC_DM_SCENAR] = "BAS"

MEMBERSET [d/PWC_DM_ITERAT] = "30"

MEMBERSET [d/Date].[p/Flag] = "PC+0"

MEMBERSET [d/PWC_DM_VLTYPE] = "061"

MEMBERSET [d/PWC_DM_GRPLOB] = (BASEMEMBER([d/PWC_DM_GRPLOB].[h/Group_LOB_Hierarchy], %GroupLOB%))

MEMBERSET [d/PWC_DM_GESFRT] = (%Geschaftsart%)

MEMBERSET [d/PWC_DM_PTGSLT] = (%PartnerGessellschaft%)

MEMBERSET [d/PWC_DM_ACCNT] = (BASEMEMBER([d/PWC_DM_ACCNT].[h/NL_Hierarchy], %Sachkonto%))

MEMBERSET [d/Measures]="PWC_KF_SALDO"

MEMBERSET [d/PWC_DM_LOKBRC]=("00093-08080","00095-09060","00095-09091")

//Define variables

VARIABLEMEMBER #SumLokaleBranche OF [d/PWC_DM_LOKBRC]

// Step 1: Calculate Total Actuals of PC-1

DATA([d/PWC_DM_LOKBRC]=#SumLokaleBranche)=RESULTLOOKUP(

   [d/Version]="public.Plan",

   [d/PWC_DM_PLANCL]="#",

   [d/PWC_DM_SCENAR]="#",

   [d/PWC_DM_ITERAT] ="#",

   [d/Date]=PREVIOUS(1),

   [d/PWC_DM_VLTYPE]="010"

  )    

//Step 2: Distribute the values

DATA()=RESULTLOOKUP(

[d/Version]="public.Plan",

[d/PWC_DM_PLANCL]="#",

[d/PWC_DM_SCENAR]="#",

[d/PWC_DM_ITERAT] ="#",

[d/Date]=PREVIOUS(1),

[d/PWC_DM_VLTYPE]="010"

   )

/ RESULTLOOKUP([d/PWC_DM_LOKBRC]=#SumLokaleBranche)* RESULTLOOKUP([d/PWC_DM_LOKBRC]="#", [d/PWC_DM_PTGSLT]="#", [d/PWC_DM_VLTYPE]="061")

//Step 3: Delete the data from "Unassigned" after the distribution

DELETE([d/PWC_DM_LOKBRC]="#",[d/PWC_DM_PTGSLT]="#", [d/PWC_DM_VLTYPE]="061")

Unfortunately it does not work. Should I also include all the dimensions in the lines of Forecast 1? And how can I make the Local Branch selection dynamic so It is applied for the specific local branches in every case (maybe a parameter with fixed value as story filter?)?

Thanks in advance.