SAC Advanced Formula dividing Revenue by Volume

[amrita_goswami]

Hi All,

I am working on a simple formula to derive Price by dividing the Net Product Revenue by Volume.

The Volume entry has UOM assigned like "EA", whereas for Revenue the UOM = "#".

In the Data Source Dimension the Revenue data is found on Datasource = "Input", whereas for Volume it is IBP , Adjustment. I have tested by assigning the Volume and the Revenue separately to the Data Statement and I can see that the correct values are being picked up. However whenever I run the division no value is getting generated. Sample Code is given below:

MEMBERSET [d/Account] = (BASEMEMBER([d/Account].[h/parentId], "NET_REV"), "#")

MEMBERSET [d/Date] = [d/Version].[p/StartMonth] TO [d/Version].[p/EndMonth]

MEMBERSET [d/Data_Source] = ("IBP", "Adj","Input")

MEMBERSET [d/Functional_Area] = ("11")

MEMBERSET [d/Measures] = ("Price")

MEMBERSET [d/Profit_Center] = (BASEMEMBER([d/Profit_Center].[h/Std_Hier], "11111"))

IF [d/Account] = BASEMEMBER([d/Account].[h/parentId], "NET_REV") THEN

DATA([d/Measures] = "Volume", [d/Data_Source] = "Calc", [d/Account] = "#", [d/Customer_Group] = "#") = RESULTLOOKUP([d/Measures] = "Volume", [d/Account] = "40000", [d/Data_Source] = "Adj")

+RESULTLOOKUP([d/Measures] = "Volume", [d/Account] = "40000", [d/Data_Source] = "IBP")

DATA([d/Measures] = "Price", [d/Data_Source] = "Calc", [d/Account] = "#", [d/Customer_Group] = "#") = RESULTLOOKUP([d/Measures] = "Rev", [d/UoM] = "#",[d/Data_Source]="Input")/

RESULTLOOKUP([d/Measures] = "Volume", [d/Data_Source] = "Calc", [d/Account] = "#", [d/Customer_Group] = "#")

ENDIF

Please provide your suggestions to resolve this issue.

Thanks,

Amrita

[N1kh1l · Accepted solution]

amrita_g_pwc

amrita_g_pwcAs I said earlier for division to work all dimensions will play a role. As Revenue and Volume are on different UOM, it will not work unless you bring them to same UOM. refer to the example below based on the sample data you provided. I have tried to show you how the code is working in backend by showing the trace snapshot ( I have removed those lines of code as it was only to see the aggregation working)

Adjust Member set for Date, Profit center and Measures accordingly. I hardcoded a single value

DATA() for price has been multiplied by RESULTLOOKUP for Volume to generate only Price for those combinations where I have Volume else it will generate price at cartesian combination of all dimensions not specified in the DATA() for price.

MEMBERSET [d/Account] = BASEMEMBER([d/Account].[h/parentId], "NET_REV")
MEMBERSET [d/Date] ="202401" //[d/Version].[p/StartMonth] TO [d/Version].[p/EndMonth]
MEMBERSET [d/Data_Source] = ("IBP", "Adj", "Input")
MEMBERSET [d/Functional_Area] = ("Revenue")
MEMBERSET [d/Measures] = ("Rev", "Volume") // Change accordinly
MEMBERSET [d/Plant] = ("31", "#")
MEMBERSET [d/UoM] = ("KIT")
MEMBERSET [d/Profit_Center] ="12000" //(BASEMEMBER([d/Profit_Center].[h/Std_Hier], "20000"))

VARIABLEMEMBER #Vol OF [d/Account]

FLOAT @Rev
FLOAT @Vol

DATA([d/Measures] = "Volume",[d/Account]=#Vol,[d/Customer_Group]="#",[d/Plant]="#",[d/Data_Source]="Calc") = RESULTLOOKUP([d/Measures] = "Volume", [d/Account] = "GROSS SALES")
@Vol= RESULTLOOKUP([d/Measures] = "Volume", [d/Account] = "GROSS SALES")
@Rev=RESULTLOOKUP([d/Measures] = "Rev",[d/Data_Source]="Input",[d/UoM]="#")
DATA([d/Measures] = "Price",[d/Account] = "#",[d/Customer_Group]="#",[d/Plant]="#",[d/Data_Source]="Calc") =@Rev/@Vol*(RESULTLOOKUP([d/Measures] = "Volume",[d/Account]=#Vol,[d/Customer_Group]="#",[d/Plant]="#",[d/Data_Source]="Calc")/RESULTLOOKUP([d/Measures] = "Volume",[d/Account]=#Vol,[d/Customer_Group]="#",[d/Plant]="#",[d/Data_Source]="Calc"))

Revenue Aggregation:

Volume Aggregation:

Output:

Alternatively you can make Volume and Revenue at same level for all dimensions including UOM except Measure and Account

Hope this helps you.

Please upvote/accept if this helps

Nikhil

[amrita_goswami · Accepted solution]

Thank you for the detailed explanation Nikhil.

Since we need to use the variables @Rev and @ Vol in this case, it would also make it necessary to use a For loop so that data for all Periods or all Profit centers does not get aggregated. Is there any way to avoid the For loop here?

[N1kh1l · Accepted solution]

amrita_g_pwc

I think you can avoid loop if all your volume Data is on UoM = "KIT", else it will start splitting various UoM. Currently UoM for price is driven by Memberset, else you have to hardcode UoM in DATA for price. See below a version of code along with aggregation. I have done both Revenue and Volume aggregation on UoM # to bring to same level .

I would suggest you try out this on more than one profit center and period and see how it works. I think dimensions not explicitly aggregated will remain intact so the data set after aggregation will be formed by PC and periods and CC etc.

MEMBERSET [d/Account] = BASEMEMBER([d/Account].[h/parentId], "NET_REV")
MEMBERSET [d/Date] ="202401" //[d/Version].[p/StartMonth] TO [d/Version].[p/EndMonth]
MEMBERSET [d/Data_Source] = ("IBP", "Adj", "Input")
MEMBERSET [d/Functional_Area] = ("Revenue")
MEMBERSET [d/Measures] = ("Rev", "Volume") // Change accordinly
MEMBERSET [d/Plant] = ("31", "#")
MEMBERSET [d/UoM] = ("KIT")
MEMBERSET [d/Profit_Center] ="12000" //(BASEMEMBER([d/Profit_Center].[h/Std_Hier], "20000"))

VARIABLEMEMBER #Vol OF [d/Account]
VARIABLEMEMBER #Rev OF [d/Account]



DELETE([d/Measures]="Price")
DATA([d/Measures] = "Volume",[d/Account]=#Vol,[d/Customer_Group]="#",[d/Plant]="#",[d/Data_Source]="Calc",[d/UoM]="#") = RESULTLOOKUP([d/Measures] = "Volume", [d/Account] = "GROSS SALES")

DATA([d/Measures] = "Rev",[d/Account]=#Rev,[d/Customer_Group]="#",[d/Plant]="#",[d/Data_Source]="Calc",[d/UoM]="#") =RESULTLOOKUP([d/Measures] = "Rev",[d/Data_Source]="Input",[d/UoM]="#")
DATA([d/Measures] = "Price",[d/Account] = "#",[d/Customer_Group]="#",[d/Plant]="#",[d/Data_Source]="Calc") =RESULTLOOKUP([d/Measures] = "Rev",[d/Account]=#Rev,[d/Customer_Group]="#",[d/Plant]="#",[d/Data_Source]="Calc",[d/UoM]="#")/RESULTLOOKUP([d/Measures] = "Volume",[d/Account]=#Vol,[d/Customer_Group]="#",[d/Plant]="#",[d/Data_Source]="Calc",[d/UoM]="#")