Formula Help - Flagging Contract Expiration Dates using Conditional Formatting

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Flagging Contract Expiration Dates using Conditional Formatting

jharke — Thu, 14 Jul 2016 09:42:09 GMT

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.

topic Re: Flagging Contract Expiration Dates using Conditional Formatting in QlikView

Formula Help - Flagging Contract Expiration Dates using Conditional Formatting

Re: Flagging Contract Expiration Dates using Conditional Formatting