SAP Analytics Cloud : Planning Data action HR use case

[#1]

Hello,

I need help for HR use case.

I have aggregated amount of salary for positions by Pay Range and Cost Center (see below).

For each Pay Range and Cost center, I have aggregated FTEs corresponding to the number of positions (see below).

Each Pay range has a min and max value.

I'd like to be able to determine the variance % between the total average salary paid (i.e. aggregated amount salary divided by aggregated FTEs) and the max value for each Pay range, but enter these values in a secondary cost center hierarchy (see the result below)

Here the two cost center hierarchies

Could be possible to achieve this with a Data action to launch for the user? I try but I cannot find a way to figure out with two hierarchies and aggregation value (with AGGREGATE_DIMENSION function).

Thanks in advance,

Etienne

[N1kh1l · Accepted solution]

etienne1110

etienne1110Apart from the calculation I see only 2 challenges you would face to solution this requirement.

• SAC stores and reads only leaf level data. If you have to read aggregated amount, either you have to go for variable member or persist (writeback) it on a leaf member.
• SAC allows to read only one hierarchy view at a time. Reading cross hierarchy within same advanced formula is not possible.

Here is what you can try as a workaround. You would need a leaf member corresponding to each parent node. For e.g. A_L, B_L for each A and B respectively. They should be outside the hierarchy as orphan root members in both hierarchy. You would also need an attribute ( I named it Member) to link the members. Something like below

Now you would need 2 Advanced Formula in your Data Action

First to do all calculations.

MEMBERSET [d/Measure]= ("AMOUNT","FTE")

VARIABLEMEMBER #AVGSALARY OF [d/Measure]

//Aggregate AMOUNT and FTE on A_L and B_L 

DATA([d/COSTCENTER]=[d/COSTCENTER].[p/Member])=RESULTLOOKUP()

// Calculate Average Salary for aggregated values using A_L and B_L

IF [d/COSTCENTER].[p/Member])="X" THEN  // only A_L and B_L have X

DATA([d/Measure]=#AVGSALARY)=RESULTLOOKUP([d/Measure]="AMOUNT")/RESULTLOOKUP([d/Measure]="FTE")

//Calculate the variance between Max salary and Avg Salary

DATA([d/Measure]="RATE")=RESULTLOOKUP([d/Measure]=#AVGSALARY)/RESULTLOOKUP([d/Measure]="MAX_SALARY",[d/COSTCENTER]="#")

ENDIF

Add second Advanced Formula within same Data Action. This one will refer to the second hierarchy and just assign the rates to A and B. As both A and B have Member attribute value A_L and B_L, RATE calculated from previous Advanced formula will be transferred to A and B as they are base members in this hierarchy along with A_L and B_L as orphan root members.

CONFIG.HIERARCHY=[d/COSTCENTER].[h/H2]
MEMBERSET [d/COSTCENTER]=BASEMEMBER([d/COSTCENTER].[h/H2] ,"ALL" )

MEMBERSET [d/MEASURE] = "RATE"

DATA()=RESULTLOOKUP([d/COSTCENTER]=[d/COSTCENTER].[p/Member])

Hope this helps !!

Br.

Nikhil

[etienne1110]

Thank you so much nikhil_1486!

It's perfect! It's exactly what I was looking for. I'm even thinking if I need or not (with this solution) for a 2nd hierarchy because I have everything store in those special nodes that you created.

Thank you,