cancel
Showing results for 
Search instead for 
Did you mean: 

unable to calculate Net profit in advance formula SAP Analytics Cloud

Prince_121083
Explorer
0 Kudos

Hi All,

my requirement is to calculate net profit using total_revenue-total expense, so for this one. I have created two variable member and assigned tot_exp and total_rev in #tot_exp and #total_rev however when I am trying to subtract both these variables then 0 net profit is coming

VARIABLEMEMBER #Tot_Exp OF [d/CG_FI_BPL_GLACCOUNT]
VARIABLEMEMBER #Tot_Rev OF [d/CG_FI_BPL_GLACCOUNT]
VARIABLEMEMBER #P_L OF [d/CG_FI_BPL_GLACCOUNT]
//-----------------------------------------------------------------------------------
IF [d/CG_FI_BPL_GLACCOUNT] = BASEMEMBER([d/CG_FI_BPL_GLACCOUNT], "Tot_Income") THEN
DATA([d/CG_FI_BPL_GLACCOUNT] = #Tot_Rev, [d/Measures] = "Amount") = RESULTLOOKUP([d/Date] = "202303", [d/Measures] = "Amount")
ELSEIF [d/CG_FI_BPL_GLACCOUNT] = BASEMEMBER([d/CG_FI_BPL_GLACCOUNT], "Tot_Exp") THEN
DATA([d/CG_FI_BPL_GLACCOUNT] = #Tot_Exp, [d/Measures] = "Amount") = RESULTLOOKUP([d/Date] = "202303", [d/Measures] = "Amount")
DATA([d/CG_FI_BPL_GLACCOUNT] =#P_L, [d/Measures] = "Amount") =
RESULTLOOKUP([d/CG_FI_BPL_GLACCOUNT] = #Tot_Rev, [d/Measures] = "Amount") -
RESULTLOOKUP([d/CG_FI_BPL_GLACCOUNT] = #Tot_Rev, [d/Measures] = "Amount")
ENDIF

View Entire Topic
N1kh1l
Active Contributor

@Prince_121083 

when I am trying to subtract both these variables then 0 net profit is coming -  Where are you seeing this 0 value

IF [d/CG_FI_BPL_GLACCOUNT] = BASEMEMBER([d/CG_FI_BPL_GLACCOUNT], "Tot_Income") THEN
DATA([d/CG_FI_BPL_GLACCOUNT] = #Tot_Rev, [d/Measures] = "Amount") = RESULTLOOKUP([d/Date] = "202303", [d/Measures] = "Amount")
ELSEIF [d/CG_FI_BPL_GLACCOUNT] = BASEMEMBER([d/CG_FI_BPL_GLACCOUNT], "Tot_Exp") THEN
DATA([d/CG_FI_BPL_GLACCOUNT] = #Tot_Exp, [d/Measures] = "Amount") = RESULTLOOKUP([d/Date] = "202303", [d/Measures] = "Amount")

ENDIF

// The PL calculation should be outside the IF block

DATA([d/CG_FI_BPL_GLACCOUNT] =#P_L, [d/Measures] = "Amount") =
RESULTLOOKUP([d/CG_FI_BPL_GLACCOUNT] = #Tot_Rev, [d/Measures] = "Amount") -
RESULTLOOKUP([d/CG_FI_BPL_GLACCOUNT] = #Tot_Exp, [d/Measures] = "Amount")

In your code you are subtracting #tot_rev by #tot_rev so it will be 0. Also you are still writing the result to a variable member #P_L, so this is virtual and you should write it back to an Account member if you want to show this in report. Also you have not mentioned how your other dimension are structured so your PL will be by those dimension memmbers.

 

Nikhil