topic Complicated validity check in QlikView

Complicated validity check

Tue, 23 Jul 2013 06:54:06 GMT

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

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 — Tue, 23 Jul 2013 07:57:10 GMT

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

Re: Complicated validity check

Tue, 23 Jul 2013 08:32:14 GMT

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