Custom Formula not working

[felipemoreira]

screenshot-9.pngEnvironment Compensation - varpay - Design Worksheet

Hi. The right syntax is not working properly

EX

if(customField67='Consultor de Negócios Pl'||customField67='Consultor de Negócios Sr',lookup('MCESemestral',customField80,1))

But if write a copy of the line below it works the way i expect.

EX

if(customField67='Consultor de Negócios Pl'||customField67='Consultor de Negócios Sr',lookup('MCESemestral',customField80,1),

if(customField67='Consultor de Negócios Pl'||customField67='Consultor de Negócios Sr',lookup('MCESemestral',customField80,1))

This way the system works properly, but i don't find reasonable to leave an extra unecessary line on the formula.

has anyone faced this along varpay worksheet?

PS: If i put the else like this doesnt work also.

if(customField67='Consultor de Negócios Pl'||customField67='Consultor de Negócios Sr',lookup('MCESemestral',customField80,1),0)

[xavierlegarrec]

sf0024213935

Thanks for confirming the solution. No unfortunately I don't think that will change. It's all about testing and how we structure our thought process and testing strategies to optimize our time when working on these formula.

The blog post on Dates is a great example of that. Different requirements lead to different formats of the columns used in the formula...

If you are an implementation partner, please ask these type of questions here (where answers can easily we searched and found by others) : https://partnercommunity.successfactors.com/t5/Compensation-Management/bd-p/Compensation-Management

And don't hesitate to be an active contributor and connect every day as you seem to have some strong experience of the module.

All the best,

Xavier

[xavierlegarrec · Accepted solution]

Hi sf0024213935

I don't think there is anything such as "right syntax" when working on custom formulas in Compensation (unfortunately). There is experience and testing and you are showing some good skills at that !

I used the same double line trick not long ago for example in the Example 2 of the IF-THEN rule section : https://blogs.sap.com/2020/09/29/how-to-use-dates-in-formulas-in-compensation-worksheets/

All the best,
Xavier

[felipemoreira · Accepted solution]

Xavier thanks man, although is a hard pill to swallow its good to hear the system has its own instabilities. But as u said is it always like this? No action to correct this by SAP?

What if i need a solution that doenst work should i request a ticket for sap intervention? How should i proceed?

Nevertheless

I solved by creating a lookup table with 2 keys instead.

Before i verified with "if" the job title - if(customField67='Consultor de Negócios Pl'||customField67='Consultor de Negócios Sr',lookup('MCESemestral',customField80,1))-

Then i solved by creating a table with 2 keys

key 1 job code customField85

key 2 goal achievement customField80

lookup('MCE',customField85,customField80,1)

Thanks for the help and the compliment. Im still a beginner!

All the best my friend.

[felipemoreira]

Solution 1

if(customField67='Consultor de Negócios Pl'||customField67='Consultor de Negócios Sr',lookup('MCESemestral',customField80,1)

lookup MCESemestral

Solution 2 with 2 keys for job code verification

key 1 customField85= job code

key 2 customField80 = target

lookup('MCE',customField85,customField80,1)

Solution 3(alternative that actually works)

if(customField67='Consultor de Negócios Sr',lookup('MCESemestral',customField80,1),

if(customField67='Consultor de Negócios Pl',lookup('MCESemestral',customField80,1),

if(customField67='Consultor de Negócios Pl',lookup('MCESemestral',customField80,1))))

mcesemestral.txt

mce.txt

mce.txt

mcesemestral.txt

[Former Member]

Why are you passing only one key to a lookup table that is asking for two?

[felipemoreira]

solution 1 is a table with 1 key using "if" to find the right job title.

Solution 2 is using 2 keys as mentioned

key 1 customField85= job code

key 2 customField80 = target

ps i attached 2 lookups

[Former Member]

The correction would be:

if(customField67='Consultor de Negócios Pl'||customField67='Consultor de Negócios Sr',lookup('MCESemestral',customField80,1),0)

"if" clauses do not work like most programming languages. You need to include the variable name a separate time.

[felipemoreira]

Thanks for sharing. I corrected the syntax in the way u said, but its not working as well as using a lookup table with 2 keys to condition the parameters. The system simply ignores formulas i dont know what to do.

[Former Member]

Can you share the lookup table itself?

[felipemoreira]

Solution 1

if(customField67='Consultor de Negócios Pl'||customField67='Consultor de Negócios Sr',lookup('MCESemestral',customField80,1)

lookup MCESemestral

Solution 2 with 2 keys for job code verification

key 1 customField85= job code

key 2 customField80 = target

lookup('MCE',customField85,customField80,1)

Solution 3(alternative that actually works)

if(customField67='Consultor de Negócios Sr',lookup('MCESemestral',customField80,1),

if(customField67='Consultor de Negócios Pl',lookup('MCESemestral',customField80,1),

if(customField67='Consultor de Negócios Pl',lookup('MCESemestral',customField80,1))))

mcesemestral.txt

mce.txt

mce.txt

mcesemestral.txt