cancel
Showing results for 
Search instead for 
Did you mean: 

SAP Analytics Cloud Advanced Formula

pragi2028
Explorer
0 Kudos

Hi All

I have to perform an operation where Rate Increase = (Salary * Rate %)/100

DATA([d/Measures] = "Rate_Increase_Dollar") = (RESULTLOOKUP([d/Measures] = "Salary") * RESULTLOOKUP([d/Measures] = "Rate_Increase_Percent"))/100 This was working fine till SAC was updated to latest version

However RESULTLOOKUP * RESULTLOOKUP is not functioning as needed.

Is there any solution for this 

 

Thanks !!

 

View Entire Topic
N1kh1l
Active Contributor
0 Kudos

@pragi2028 

DATA([d/Measures] = "Rate_Increase_Dollar") = (RESULTLOOKUP([d/Measures] = "Salary") * RESULTLOOKUP([d/Measures] = "Rate_Increase_Percent"))/100

This can only work if both Salary and Rate % are stored on same combination for other dimensions in the model. If your rates are stored at other intersection then salary then specify those dimension values within the resultlookup.

Please post your data intersection from model for both Salary and Rate % sample records.

 

Nikhil

pragi2028
Explorer
0 Kudos

@N1kh1l

pragi2028_0-1709054359585.pngpragi2028_1-1709054369030.pngpragi2028_2-1709054379693.png

Please find the sample data from model where the Rate % is user input and the Rate increase should be calculated based on that 

 

MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/YQM], "(all)"))
MEMBERSET [d/Measures]=("Salary","Rate_Increase_Percent","Rate_Increase_Dollar")
MEMBERSET [d/Hiring_Date] = (BASEMEMBER([d/Hiring_Date].[h/YQM] ,"(all)" ))
IF [d/Hiring_Date].[p/YEAR] = "2023" THEN
IF [d/Hiring_Date].[p/MONTHDESC] = ("Dec", "Nov", "Oct") THEN
//No Cal
ELSE
DATA([d/Measures] = "Rate_Increase_Dollar") =  RESULTLOOKUP([d/Measures] = "Salary") * RESULTLOOKUP([d/Measures] = "Rate_Increase_Percent")
ENDIF
ENDIF
 
IF [d/Hiring_Date].[p/YEAR] != ("2024", "2023") THEN
DATA([d/Measures] ="Rate_Increase_Dollar") = RESULTLOOKUP([d/Measures] = "Salary") * RESULTLOOKUP([d/Measures] = "Rate_Increase_Percent")
ENDIF
 
Please find the full logic above 
Thanks for the reply !
N1kh1l
Active Contributor
0 Kudos

I see the rates as null, not sure if i am seeing the right column ( last image, last 2 columns)

pragi2028
Explorer
0 Kudos

@N1kh1lThe rate increase % will be user input in the story and when the data action is triggered Rate increase dollar should get calculated. So it is null.

I have entered some data to Rate %. But the Rate increase dollar is not getting calculated. Last column is Rate increase %.

pragi2028_0-1709056691704.png

 

N1kh1l
Active Contributor
0 Kudos
Can you post the log of this DA from DA monitor
pragi2028
Explorer
0 Kudos

@N1kh1l

pragi2028_0-1709058427541.pngpragi2028_1-1709058451668.png

Thanks

N1kh1l
Active Contributor
0 Kudos

Make sure you have both Salary and rate % in Budget version. Also try adding MEMBERSET for other dimensions. If your dimension has members outside hierarchy add the CONFIG.HIERARCHY.INCLUDE_MEMBERS_NOT_IN_HIERARCHY = [d/DimensionName1],[d/DimensionName2]

To narrow down the issue run it for one employee by hardcoding the memberset,

pragi2028
Explorer
0 Kudos

@N1kh1lWhen I upload data for Rate % in model and then run the DA its calculating the Rate Increase value. But when I leave the Rate % null in the model and directly enter the value in Story and then run the DA its not producing any results. Can you please help me with this.

Thanks

N1kh1l
Active Contributor
0 Kudos
Ok. Make sure your story is writing the rate % to same data intersection where your file is uploading. Also when you enter in story, are you publishing the data after entering and then running the DA?
pragi2028
Explorer
0 Kudos
@N1kh1l yes Im running DA only after publishing the data. And rate % is getting written to same data Intersection . but no results after running the DA