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

    Anybody want to parse this formula

    Nick Osdale-Popa

      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="R", /Then/

          EmpRTRate,

        /Else/

          IF(Time="O", /Then/

             EmpOTRate,

          /Else/

             EmpDTRate)),

          /End If/

        /End If/

      /Else/

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

          IF(Time="R", /Then/

            CCRTRate,

          /Else/

            IF(Time="O", /Then/

              CCOTRate,

            /Else/

              CCDTRate)),

            /End If/

          /End If/

        /Else/

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

            IF(OnSite, /Then/

              IF(Time="R", /Then/

                SKLWCRTRate,

              /Else/

                IF(Time="O", /Then/

                  SKLWCOTRate,

                /Else/

                  SKLWCDTRate)),

                /End If/

              /End If/

            /Else/

              IF(Time="R", /Then/

                SKLRTRate,

              /Else/

                IF(Time="O", /Then/

                  SKLOTRate,

                /Else/

                  SKLDTRate))),

                /End If/

              /End If/

            /End If/

          /Else/

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

              IF(Time="R", /Then/

                UNRTRate,

              /Else/

                IF(Time="O", /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
          Bruce _

          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
            Nick Osdale-Popa

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

            • Anybody want to parse this formula
              Grant Perkins

              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
                Nick Osdale-Popa

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

                • Anybody want to parse this formula
                  Grant Perkins

                  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
                    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

                    • Anybody want to parse this formula
                      Grant Perkins

                      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
                        RalphB _

                        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
                          Nick Osdale-Popa

                          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

                          groupadjustment

                          group

                          SORT

                          HyperLink

                          Category

                          COMPANYSORT

                          UNIONCODE

                          SKILLCODE

                          UNION

                          SKILL

                          JOB

                          Match

                          RATE

                          OnSite

                          Total

                          -


                          External Lookups

                           

                          Data Source:

                               K:MC_DBBilling.mdb - EmployeeRates

                          Links:

                               EmployeeNumber links to EMPNum

                               JobNumber links to JOBNum

                          Imports:

                               RTRate imports to EmpRTRate

                               OTRate imports to EmpOTRate

                               DTRate imports to EmpDTRate

                               ID imports to EmpID

                           

                          Data Source:

                               K:MC_DBBilling.mdb - SkillRates

                          Links:

                               JobNumber links to JOBNum

                               Skill links to SKILLTrap

                          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

                          Links:

                               JobNumber links to JOBNum

                               CostCode links to COSTCODE

                          Imports:

                               ID imports to CCID

                               RTRate imports to CCRTRate

                               OTRate imports to CCOTRate

                               DTRate imports to CCDTRate

                           

                          Data Source:

                               K:MC_DBBilling.mdb - UnionRates

                          Links:

                               JobNumber links to JOBNum

                               UnionCode links to UnionAbbrv

                          Imports:

                               ID imports to UNID

                               RTRate imports to UNRTRate

                               OTRate imports to UNOTRate

                               DTRate imports to UNDTRate

                          • Anybody want to parse this formula
                            Grant Perkins

                            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