10 Replies Latest reply: May 15, 2014 9:51 AM by Grant Perkins

Anybody want to parse this formula

V7.01. Was wondering if there's a better solution to this formula. It's trying to calculate a rate based on employeeid (EmpID), CostCode(CCID), Skill (SKID), or Union (UNID), then also whether the employee worked Regular, Over, or Double time. If a rate can't be determined, it's returning -999.

[font="courier"]IF( .Not. IsNull(EmpID), /Then/

IF(Time=&quot;R&quot;, /Then/

EmpRTRate,

/Else/

IF(Time=&quot;O&quot;, /Then/

EmpOTRate,

/Else/

EmpDTRate)),

/End If/

/End If/

/Else/

IF( .Not. IsNull(CCID), /Then/

IF(Time=&quot;R&quot;, /Then/

CCRTRate,

/Else/

IF(Time=&quot;O&quot;, /Then/

CCOTRate,

/Else/

CCDTRate)),

/End If/

/End If/

/Else/

IF( .Not. IsNull(SklID),/Then/

IF(OnSite, /Then/

IF(Time=&quot;R&quot;, /Then/

SKLWCRTRate,

/Else/

IF(Time=&quot;O&quot;, /Then/

SKLWCOTRate,

/Else/

SKLWCDTRate)),

/End If/

/End If/

/Else/

IF(Time=&quot;R&quot;, /Then/

SKLRTRate,

/Else/

IF(Time=&quot;O&quot;, /Then/

SKLOTRate,

/Else/

SKLDTRate))),

/End If/

/End If/

/End If/

/Else/

IF( .Not. IsNull(UNID), /Then/

IF(Time=&quot;R&quot;, /Then/

UNRTRate,

/Else/

IF(Time=&quot;O&quot;, /Then/

UNOTRate,

/Else/

UNDTRate)),

/End If/

/End If/

/Else/

-999))))

/End IF/

/End IF/

/End IF/

/End IF/[/font][/quote]

• Anybody want to parse this formula

Nick

Seems a bit complicated, but is it working?

The only thing I could suggest (to a guy who knows Monarch better than I do) would be a look up table. However that would probaly raise more issues than solving this long if-then-else statement.

Bruce

• Anybody want to parse this formula

Yup, it works as advertised. Just wondering if I'm missing a "simpler" solution.

• Anybody want to parse this formula

Nick,

I like your use of comments.   :cool:  Certainly helps a lot to make the nested IFs more readable when the codes are so similar.

My initial thoughts are the same as Bruce's. Using a lookup table would be the obvious alternative. I guess you could could concatenate the possible combinations of the different variables into a single 'pseudo code' and simply look up a rate based on that code. Same effect but a little easier to see and 'interpret' than the nest ifs.

Any future changes to the combinations might be easier to manage through a table than through a formula. However if the potential for future changes is low and the formula does the job there may be little point in changing the model.

By my reckoning you still have around 50% of the available nesting levels unused!

Grant

Originally posted by Nick Osdale-Popa:

Yup, it works as advertised. Just wondering if I'm missing a "simpler" solution. /b[/quote]

• Anybody want to parse this formula

Pray tell, how would one set up the lookup table? scratches head

• Anybody want to parse this formula

Nick,

Looking at your nested if formula I take it that you are checking values of CCID, SKID and UNID (providing there is a valid EMPID value)and whether the employee is working at a regular, overtime or double time rate in order to identify a rate to use for a calculation.

The IF contruct suggests that the choice of rate is dependent upon a hierarchy based on the rules obtaned from above. For each resulting hierarchical selection a rate.

The SKID code also requires identification of whether the employee is working  on site or not.

So it appears that it might be possible to identify a single code for each component of the decision process with limited values;

[font="courier"]CCID   SKID   ONSITE   UNID

R      RT      Y       R

C      OT      N       D

O      DT

WCRT

WCDT

WCOT[/font][/quote]Assuming that these codes are derived as separate fields, concatenating them into a single field might give something like

RRTYR

RRTNR

RRTYD

RRTND

ROTYR

ROTNR

ROTYD

ROTND

CRTYR

CRTNR

CRTYD

CRTND

COTYR

COTNR

COTYD

COTND

... and so on.

Some of the combinations probably do not (or should not!) occur.

Each combination would have a specific rate (\$ rate or rate code) associated with it, just as your nest IF formula defines. SO a calcualted field based on the concatenated codes could be used to look up the rate value from a table (or the rate code to look up in another table).

The lookup table would be internal for standard Monarch (for those versions which offer the feature) or might be an external database lookup if using the Pro version. (That means the table could be maintained as part of some other process or system if required.)

There may be a simpler way of viewing this since many of the codes may end up using the same rate (I'm guessing.) So the concept may need to be adapted accordingly.

The only real benefit would be easier maintenance for future changes of possible combinations or payment rates or simply the model, should it become necessary.

I may have missed something in your formula that would make this approach inappropriate as presented but I think the idea should have some merit. I hope.

What do you think?

Grant

Originally posted by Nick Osdale-Popa:

Pray tell, how would one set up the lookup table? scratches head /b[/quote]

[size="1"][ March 31, 2004, 04:01 AM: Message edited by: Grant Perkins ][/size]

• Anybody want to parse this formula

Nick

Grant's suggestion is bang on.

Of course the down side is that you will have to enumerate all the possiblities (72) in Monarch or an external table.

Depending on how often you change employee rates setup this might be easier to manage than the orginal if-then statement.

Bruce

• Anybody want to parse this formula

Bruce, thanks.

There is a possibility that careful ordering of the codes might reduce the total number of codes required, for instance where a specific code overrides all others it might be easier to reduce the possibilities to just that code and then do the look up. And of course some combinations may be unused.

It is difficult to be sure about such ideas without knowing the details of the data and rates quite well.

Grant

Originally posted by Bruce:

Nick

Grant's suggestion is bang on.

Of course the down side is that you will have to enumerate all the possiblities (72) in Monarch or an external table.

Depending on how often you change employee rates setup this might be easier to manage than the orginal if-then statement.

Bruce /b[/quote]

• Anybody want to parse this formula

Another possibility is to set up a lookup table for each component and then do a calculation to get the individual rate.  This would give you more flexibility because as soon as you think one concantation will not be used, it will be and would eliminate re-calculation errors when rates change.

After 10 years of doing payroll, I learned when you think you have seen every combination possible, there is a new one.

• Anybody want to parse this formula

Whew!  :eek:  That's a lot of code combinations!  Not sure if it will help, but below is the list of fields and external lookups for the rates. The rates have a hierarchy to them, such as:

Employee Rate[/b]

Cost Code[/b]

Employee Skill[/b] (and whether the person was on site or not)

Union[/b]

And if a rate can't be determined (ie an error), -999 is returned.

So, based on that, are you still not using multiple nested IF() functions to build the Code Combinations?  Also, based on that Code, how do you get it to return the different Rate Fields?

As you can see, the External Lookups are also based on Job #. Currently, I'm only dealing with one Job and the breakdown is such:

46 have Employee based rate

0 have any costcode based rates

12 are based on Skill and thus 12 Onsite rates too

0 have any union based rates

Future Jobs obviously can have more relations. Also there is a table in the database for Title Based rates. Currently that hasn't been implemented, but I think it would fall after Employee in the hierarchy.

Hopefully this sheds more light on the subject. I'm just wondering if I was overlooking something.

Fields

Name

EMPNum

EMPLOYEE

SAT

SUN

MON

TUE

WED

THR

FRI

JOBNum

trappedgroup

SKILLTrap

UNIONTrap

PHASE

PRDATE

COMPANY

COSTCODE

UnionAbbrv

Time

WrkComp

EmpID

EmpRTRate

EmpOTRate

EmpDTRate

CCID

CCRTRate

CCOTRate

CCDTRate

SklID

SklRTRate

SklOTRate

SklDTRate

SklWCRTRate

SklWCOTRate

SklWCDTRate

UNID

UNRTRate

UNOTRate

UNDTRate[/b]

JobFilter

TOTAL Hours

group

SORT

Category

COMPANYSORT

UNIONCODE

SKILLCODE

UNION

SKILL

JOB

Match

RATE

OnSite

Total

-

External Lookups

Data Source:

K:MC_DBBilling.mdb - EmployeeRates

Imports:

RTRate imports to EmpRTRate

OTRate imports to EmpOTRate

DTRate imports to EmpDTRate

ID imports to EmpID

Data Source:

K:MC_DBBilling.mdb - SkillRates

Imports:

RTRate imports to SklRTRate

OTRate imports to SklOTRate

DTRate imports to SklDTRate

WCRTRate imports to SklWCRTRate

WCOTRate imports to SklWCOTRate

WCDTRate imports to SklWCDTRate

ID imports to SklID

Data Source:

K:MC_DBBilling.mdb - CostCodeRates

Imports:

ID imports to CCID

RTRate imports to CCRTRate

OTRate imports to CCOTRate

DTRate imports to CCDTRate

Data Source:

K:MC_DBBilling.mdb - UnionRates

Imports:

ID imports to UNID

RTRate imports to UNRTRate

OTRate imports to UNOTRate

DTRate imports to UNDTRate

• Anybody want to parse this formula

Nick,

True, a lookup table is still part of a form of 'nested if' or prior decision process. But it may prove easier to adapt and maintain if only because the separate parts of the progressive analysis are easier to see when presented in isolation from the rest of the formula.

In a heirarchical construct I guess you will end up with a single 'code' in reality and a qualifier for certain attributes ('Onsite' for example). The combination exists because it sits at the top of the hierarchy for that employee on that project. So the reality is that far fewer codes will exist than the number of possible combinations? Basically only those that come out of your nested IF formula.

I'll try to work a more useful response in the next day or so.

Grant