UCCE Script Editor Custom Function formula help

[Nathan Luk]

Hi guys,

I was wondering if there are any formula gurus who can help me out, I'm trying to create 4 different functions, one takes an enterprise skill group and returns the minimum expected delay / expected wait time. The other 3 are meant to take 2, 3 and 4 different enterprise skill groups and return which of them has the minimum expected delay (this is being used in a pre-routing type scenario to determine where to translation route the call to in order to minimize needing to have the call answered in a different PG from where it's queued). I have the formulas worked out for comparing 2 and 3 different values and figuring out which is lowest but the formula for returning the lowest out of 4 values is doing my head in, hoping that someone has done this before or is an actual developer who nested if statements doesn't do their head in unlike me!:

ESG_MED Function - 1 parameter - Takes Enterprise Skill Group (ESG) Name and returns Minimum Expected Delay (MED):

ValidValue(((EntSkill.Default\%1%.CallsQueuedNow+1)

                                    *

                                    (ValidValue(EntSkill.Default\%1%.AvgHandledCallsTimeTo5,20))

                                    /max(

                                    EntSkill.Default\%1%.Ready,

                                    (EntSkill.Default\%1%.TalkingIn

                                    +

                                    EntSkill.Default\%1%.TalkingOut

                                    +

                                    EntSkill.Default\%1%.TalkingOther))

                                    ),100)

ESG_MED_2 Function - 2 parameters - Takes 2 Enterprise Skill Groups and returns which ESG has the lowest MED (1 for first parameter or 2 for second parameter, also returns 0 if MED across all ESGs=0):

If(ESG_MED(%1%)+ESG_MED(%2%)=0,0,if(ESG_MED(%1%)>ESG_MED(%2%),2,1))

ESG_MED_3 Function - 3 parameters - Same as the previous except takes 3 ESGs:

if(ESG_MED(%1%)+ESG_MED(%2%)+ESG_MED(%3%)=0,0,if(ESG_MED(%1%)< ESG_MED(%2%),if(ESG_MED(%1%)< ESG_MED(%3%),1,3),if(ESG_MED(%1%)< ESG_MED(%3%),2,if(ESG_MED(%2%)< ESG_MED(%3%),2,3))))

ESG_MED_4 Function - 4 parameters - Same as the previous except takes 4 ESGs:

???

Your help is much appreciated!

Cheers,

Nathan

[Jameson Gagnepain · Accepted solution]

Nathan,

It seems to me it may be simpler to bring in some ANDs (&&) so that you don't confuse yourself with too many IF statements. Here's everything with some whitespace for clarity:

if(ESG_MED(%1%)+ESG_MED(%2%)+ESG_MED(%3%)+ESG_MED(%4%)=0,0,
if(ESG_MED(%1%)<ESG_MED(%2%)&&ESG_MED(%1%)<ESG_MED(%3%)&&ESG_MED(%1%)<ESG_MED(%4%),1,
if(ESG_MED(%2%)< ESG_MED(%3%)&&ESG_MED(%2%)<ESG_MED(%4%),2,
if(ESG_MED(%3%)< ESG_MED(%4%),3,4))))

And here's the compacted version:

if(ESG_MED(%1%)+ESG_MED(%2%)+ESG_MED(%3%)+ESG_MED(%4%)=0,0,if(ESG_MED(%1%)<ESG_MED(%2%)&&ESG_MED(%1%)<ESG_MED(%3%)&&ESG_MED(%1%)<ESG_MED(%4%),1,if(ESG_MED(%2%)<ESG_MED(%3%)&&ESG_MED(%2%)<ESG_MED(%4%),2,if(ESG_MED(%3%)<ESG_MED(%4%),3,4))))

Your ESG_MED_3 function could be similarly simplified:

if(ESG_MED(%1%)+ESG_MED(%2%)+ESG_MED(%3%)=0,0,if(ESG_MED(%1%)<ESG_MED(%2%)&&ESG_MED(%1%)<ESG_MED(%3%),1,if(ESG_MED(%2%)<ESG_MED(%3%),2,3,4)))

-Jameson

[Nathan Luk]

Awesome, thanks very much for that Jameson, it appears Script Editor also has a limit of the number of functions you can use within a custom function and was saying the function exceeded that so I had to expand the ESG_MED functions within the formula out into the full formula making it horrifically long but it looks like it works, thanks for your help!