Complicated validity check

[#1]

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      

[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),

[#3]

This is not correct we have red, green or yellow on our dashboard.

Of course we checked for being Completed and we had a training that was completed okay and not expiring and in red on our dashboard. this training record must be green in the mandatory table.

John