2 Replies Latest reply: Jul 23, 2013 4:32 AM by John Ophof RSS

    Complicated validity check

    John Ophof

      I have a model with employees and training data another table is filled with trainingcode and 1 if the training expires.

      Expired trainings have a validity age of 18 months.

      1 = green, 2 = yellow, 3 = red

      The gauge setting is like this (mand_training is table with expiration info, training_ is training excel data, referenceYear is the run date.

       

      = if (

          (TRAINING_REGISTRATION_STATUS='Completed'

              AND MAND_TRAINING_EXPIRES = 1

              AND((vReferenceYear - TRAINING_COMPLETION_YEAR)*12 + vReferenceMonth - TRAINING_COMPLETION_MONTH) < 14)

              OR (TRAINING_REGISTRATION_STATUS='Completed'

              AND MAND_TRAINING_EXPIRES = 0),

              1,

              if( TRAINING_REGISTRATION_STATUS='Completed' AND

                          MAND_TRAINING_EXPIRES = 1 AND

                          ((vReferenceYear - TRAINING_COMPLETION_YEAR)*12 + vReferenceMonth - TRAINING_COMPLETION_MONTH) < 18 AND

                          ((vReferenceYear - TRAINING_COMPLETION_YEAR)*12 + vReferenceMonth - TRAINING_COMPLETION_MONTH) >= 14,2,

                         

              if( TRAINING_REGISTRATION_STATUS='Completed' AND

                          MAND_TRAINING_EXPIRES = 1 AND

                          ((vReferenceYear - TRAINING_COMPLETION_YEAR)*12 + vReferenceMonth - TRAINING_COMPLETION_MONTH) >= 18,3,3)

                          ))

             

      Sometimes we miss a green (validity == 1) when training does not expire ( MAND_TRAINING_EXPIRES = 0 ). but why and how to improve this long if?

       

      John      

        • Re: Complicated validity check
          Gysbert Wassenaar

          This bit always returns 3 so you can replace the whole statement with just the value 3.

           

          if( TRAINING_REGISTRATION_STATUS='Completed' AND

                              MAND_TRAINING_EXPIRES = 1 AND

                              ((vReferenceYear - TRAINING_COMPLETION_YEAR)*12 + vReferenceMonth - TRAINING_COMPLETION_MONTH) >= 18,3,3)

                              ))

           

          If you miss a green when a training is not expired then the most likely cause is that the status is not 'Completed':

           

          OR (TRAINING_REGISTRATION_STATUS='Completed'

                  AND MAND_TRAINING_EXPIRES = 0),