cancel
Showing results for 
Search instead for 
Did you mean: 

Issue with nested if-then formula in compensation worksheet

swagatikapanda
Participant
0 Kudos
107

Dear Experts,

Need your help in below issue. I have used below nested-if formula used for a custom field Current Pay Bucket. After launching the form, the field shows N/A (which is a standard SF value for Not applicable). If I input merit or promotion, then the field is populated correctly as per formula below. If the worksheet is saved or refreshed the value is again going away. Please help.

if(payGuideMin>0 && payGuideMax>0 && customCurRate>0 && customDefLocale !="" ,
if(customDefLocale="en_US" || customDefLocale="en_GB",
if(customCurRate<payGuideMin,"Below Min",
if(customCurRate>payGuideMax,"Above Max",
if(curRatio>0 && curRatio<90,"Developing",
if(curRatio>89.999 && curRatio<110,"Competitive",
if(curRatio>109.999,"Above Market",
"NA")))))),
if(customDefLocale="es_MX",
if(customCurRate<payGuideMin,"Por debajo del mínimo",
if(customCurRate>payGuideMax,"Por encima del máximo",
if(curRatio>0 && curRatio<90,"En desarrollo",
if(curRatio>89.999 && curRatio<110,"Competitivo",
if(curRatio>109.999,"Por encima del mercado",
"NA")))))),
if(customDefLocale="fr_CA",
if(customCurRate<payGuideMin,"En dessous du minimum",
if(customCurRate>payGuideMax,"Au-dessus du maximum",
if(curRatio>0 && curRatio<90,"En cours de perfectionnement",
if(curRatio>89.999 && curRatio<110,"Compétitif",
if(curRatio>109.999,"Supérieur au marché",
"S.O.")))))),
"No Pay Range or Default Locale")

 

Thanks,

Swagatika

@xavierlegarrec 

View Entire Topic
xavierlegarrec
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi @swagatikapanda 

Are there editable fields other than standard fields on your template (even if not referenced in the formula) ?

I just fixed this issue yesterday which is the same behavior than what you describe (scroll up to see the problem as it was explained by Phil).

https://community.sap.com/t5/human-capital-management-blogs-by-sap/dates-and-roundings-in-compensati...

You can also go back up and look at Example 5 where I inserted the code.

What I usually do to troubleshoot these issues is I break down the parts of the formula into different columns. That will help you single out which part is creating issues.

Thanks 

Xavier

lesleyr
Product and Topic Expert
Product and Topic Expert
0 Kudos

As Xavier mentioned, try breaking your formula down.

Remember the syntax is if(condition, true, false). When using multiple if's, you will have your "true" value after each "if" and then at the end have your "false". In your formula you aren't doing this properly on each line.

For example:

What do you want to happen for the first line? The second line? What happens if someone's DefLocal is en_US or en_GB? Are you maybe missing some "&&" and "||" conditions? I think you're getting N/A's because you have some flaws in your conditions/logic.

if(payGuideMin>0 && payGuideMax>0 && customCurRate>0 && customDefLocale !="" ,
if(customDefLocale="en_US" || customDefLocale="en_GB",

For these two lines you want "Below Min" when the rate is less than the min and "Above Max" when rate is above the max but in the above first 2 lines you haven't given the "true" condition when these criteria are met.if(customCurRate<payGuideMin,"Below Min",
if(customCurRate>payGuideMax,"Above Max",

I suggest building out your formula in smaller pieces so that you can easily see where the formula/you are going wrong and it will help you to correct the issues.