SAC Planning Data Action & Allocation in a single step

[GavinMonteiro3]

Hi All,

In SAP Analytics Cloud (SAC) Planning, we have a requirement to transfer values (from one cost center to another) booked on the head office, in %'s that would be specified by the users.

In the below example, Head office has Rs 1,000/- expenditure, the user would mention 50% to be allocated to Warehouse & 20% to be allocated to Ecom team 

BEFORE Allocation

After he mentions the %'s, the expectation is that the values in the columns should look as seen below: -

AFTER Allocation

How can we achieve this in SAC using a Data action step (since the user is using the microsoft office SAC excel add in & hence we wouldnt want to use the distribute/redistribute feature present in the story).

@N1kh1l if you could please provide any inputs in achieving this.

Regards,

Gavin.

[N1kh1l · Accepted solution]

@GavinMonteiro3 

After he mentions the %'s, the expectation is that the values in the columns should look as seen below: -

AFTER Allocation

The Data Action run will be required to see the results. The Data Action currently cannot execute automatically on Data save.

The Data Action would be very simple. You can also use standard Allocation step for this.  If you use Advance Formula for this

MEMBERSET [d/Account]="Amount"

MEMBERSET [d/costcenter] = ("WH","ECOM") // list of recievers

DATA()=RESULTLOOKUP([d/Account]="AllocPerc")*RESULTLOOKUP([d/costcenter]="HQ")

DATA.APPEND([d/costcenter]="HQ")=RESULTLOOKUP()*-1 // -ve offset on HQ

You can adjust this according to your requirement. For simplicity store the Allocation % on same intersection as Amount but just a different account.

Hope this helps !!

Nikhil

Nikhil

[GavinMonteiro3 · Accepted solution]

Thanks N1kh1l for your help.

[GavinMonteiro3]

For others who have a similar requirement, I created a Data action

DATA([d/Profit_center] = "Ecom allocated") = RESULTLOOKUP([d/Profit_center] = "HO") * %Ecom_Percentage% / 100

DATA([d/Profit_center] = "RM allocated") = RESULTLOOKUP([d/Profit_center] = "HO") * %RM_Percentage% / 100
DATA.APPEND([d/Profit_center] = "HO") = RESULTLOOKUP([d/Profit_center] = "RM allocated") * -1 // -ve offset on HO
DATA.APPEND([d/Profit_center] = "HO") = RESULTLOOKUP([d/Profit_center] = "Ecom allocated") * -1 // -ve offset on HO

I kept the data append in the 2nd step for both Ecom & Raw material to ensure that the base value considered for both the steps is the same.