Announcements
cancel
Showing results for
Did you mean:
Contributor III

How to change a cells color based on a condition.

Hi All,

I am working on Qlik Sense and have a table built listing clients and contract dates. Once a year these contracts need to be updated and that can take time.

What I want to do is change the color of the cell if it is within 90,60, and 30 days of expiring.

Example:

Annual Date = 01/01/2016

Annual Due = 01/01/2017

Date: 10/01/2016 = Cell turns Yellow

11/01/2016 = Cell turns Orange

12/01/2016 = Cell turns Red

This way when can open this report and always see who is approaching renewal.

I am working withing the Chart Background Color expression to do this and currently have only been successful with yellow and green.

Expression:

IF(([annual.autoCalendar.Date]+365)-TODAY()<90,

RGB(241,244,66),

IF(([annual.autoCalendar.Date]+365)-TODAY()<60,

RGB(244,170,66),

IF(([annual.autoCalendar.Date]+365)-TODAY()<30,

RGB(244,66,66),

RGB(66,244,69)

)

)

)

Is there a better way to do this? I kind of need to be able to do this within the Chart and not the Data Load Editor.

1 Solution

Accepted Solutions
MVP

Shouldn't this be in the opposite order?

IF(([annual.autoCalendar.Date]+365)-TODAY()<30,

RGB(244,66,66),

IF(([annual.autoCalendar.Date]+365)-TODAY()<60,

RGB(244,170,66),

IF(([annual.autoCalendar.Date]+365)-TODAY()<90,

RGB(241,244,66),

RGB(66,244,69)

)

)

)

2 Replies
MVP

Shouldn't this be in the opposite order?

IF(([annual.autoCalendar.Date]+365)-TODAY()<30,

RGB(244,66,66),

IF(([annual.autoCalendar.Date]+365)-TODAY()<60,

RGB(244,170,66),

IF(([annual.autoCalendar.Date]+365)-TODAY()<90,

RGB(241,244,66),

RGB(66,244,69)

)

)

)

Contributor III
Author

You are very right. Sunny thank you! The rush of the holidays must have fried my brain to forget such a small detail!