Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Formula Help - Flagging Contract Expiration Dates using Conditional Formatting

Hello!

I am fairly new to QlikView and I am hoping someone out there can help me with some formulas and conditional formatting.

I am creating a contract database for my company's suppliers. Many of the supplier contracts require x number of days termination notice (a mix between 30, 60, & 90 days) prior to the actual contract expiration date. I want the app to recognize the contracts that are coming close to their expiration date and that require x number of days written notice to be flagged 90 days before the written notice is due to the supplier. I know how to conditional format a column but not the entire row. Also I am not sure what formulas to use to get the app to recognize the warnings that I described.

Note: not all of the contract have unique numbers or numbers at all, I have been using SupplierID as a key field.

EXAMPLE 1:

Contract A is set to expire 1/27/16. The supplier requires 90 days written termination notice which would be due by 10/29/16. I want to be warned an additional 90 days prior to when the written termination notice is due to the supplier - the entire contract's row of information turns Yellow. At 60 days, I would like the warning to change from Yellow to Orange. At 30 days, I would like the warning to change from Orange to Red. I would like warnings to stay the correct warning level color until the next warning level hits.

8/1/16  - 8/30/16 = Yellow

8/31/16 - 9/29/16 = Orange

9/30/16 - 10/29/16 = Red

Written Termination Notice due to supplier by 10/29/16.

Contract Expires 1/27/16.

EXAMPLE 2:

Contract B is set to expire 1/27/16. The supplier requires 60 days written termination notice which would be due by 11/29/16. I want to be warned an additional 90 days prior to when the written termination notice is due to the supplier - the entire contract's row of information turns Yellow. At 60 days, I would like the warning to change from Yellow to Orange. At 30 days, I would like the warning to change from Orange to Red. I would like warnings to stay the correct warning level color until the next warning level hits.

9/1/16  - 9/30/16 = Yellow

10/1/16 - 10/30/16 = Orange

10/31/16 - 11/29/16 = Red

Written Termination Notice due to supplier by 11/29/16.

Contract Expires 1/27/16.

EXAMPLE 3:

Contract C is set to expire 1/27/16. The supplier requires 30 days written termination notice which would be due by 12/29/16. I want to be warned an additional 90 days prior to when the written termination notice is due to the supplier - the entire contract's row of information turns Yellow. At 60 days, I would like the warning to change from Yellow to Orange. At 30 days, I would like the warning to change from Orange to Red. I would like warnings to stay the correct warning level color until the next warning level hits.

10/1/16  - 10/30/16 = Yellow

10/31/16 - 11/29/16 = Orange

11/30/16 - 12/29/16 = Red

Written Termination Notice due to supplier by 12/29/16.

Contract Expires 1/27/16.

Any help would be greatly appreciated.

1 Reply
jharke
Creator
Creator

Hello Jessica

See attached file for some coloring. I have put some formatting in the Background Color of the Supplier Name (double click on it).

You have to adjust the RedGreenBlue amounts and the number of days yourself.