Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.