topic Re: How do I change background/text colour based on a date met in QlikView

How do I change background/text colour based on a date met

martin_hamilton — Wed, 25 Nov 2020 16:16:04 GMT

I have a formula which places a background onto a cell when the date is less than today - which is working fine.

= if(Sub_Contract_End_Date <= Today(), YELLOW())

How do I amend to pick up dates greater than today and have a differing colour for them, i have got to the below but cant think of how to introduce an OR statement

= if(Sub_Contract_End_Date <= Today(), YELLOW())= if (Sub_Contract_End_Date => Today()+30, RED())

Re: How do I change background/text colour based on a date met

trdandamudi — Fri, 02 Sep 2016 02:23:50 GMT

May be as below:

= if(Sub_Contract_End_Date <= Today(), YELLOW(), if (Sub_Contract_End_Date >= Today()+30, RED() ))

Re: How do I change background/text colour based on a date met

sujayrpatil — Fri, 02 Sep 2016 04:56:46 GMT

Hi Martin,

I think this would do, try it .

= if(Sub_Contract_End_Date <= Today(), YELLOW(), RED() )

Re: How do I change background/text colour based on a date met

millan123 — Fri, 02 Sep 2016 05:15:28 GMT

Please Try this

= if(Sub_Contract_End_Date <= Today(), YELLOW(), if (Sub_Contract_End_Date >= Today(), RED() ))

Re: How do I change background/text colour based on a date met

jeevays7 — Fri, 02 Sep 2016 05:36:05 GMT

Hi Martin,

Try this,

= IF(Sub_Contract_End_Date <= Today(), YELLOW(),

IF(Sub_Contract_End_Date >= Date(Today()+30), RED()))

Re: How do I change background/text colour based on a date met

amit_saini — Fri, 02 Sep 2016 05:48:36 GMT

Hi Martin,

This might help u:

https://community.qlik.com/message/823603#823603

Thanks,

Re: How do I change background/text colour based on a date met

Chanty4u — Fri, 02 Sep 2016 06:00:09 GMT

try below

Re: How do I change background/text colour based on a date met

martin_hamilton — Sun, 04 Sep 2016 20:27:09 GMT

Hi All

Unfortunately these formulas are highlighting all future dates in RED as well when I actually only want the dates 30 days from todays date. I was thinking something like the below might work but cant get the formulas right:

'>=' & Date(Today(), 'DD/MM/YYYY') & '<=' & Date(Today()+30

Thanks

Re: How do I change background/text colour based on a date met

trdandamudi — Mon, 05 Sep 2016 03:28:06 GMT

May be as below:

= if(Sub_Contract_End_Date <= Today(), YELLOW(), if (Sub_Contract_End_Date > Today() AND Sub_Contract_End_Date <= Today()+30, RED() ))

Re: How do I change background/text colour based on a date met

jeevays7 — Tue, 06 Sep 2016 05:55:08 GMT

Try This,

1. below todays dates are yellow and 30th day from today only is red.

= IF(Sub_Contract_End_Date <= Today(), YELLOW(),

IF(Sub_Contract_End_Date = Date(Today()+30), RED()))

2. This only for 30 days date in red from today.

= IF(Sub_Contract_End_Date <= Today(), YELLOW(),

IF(Sub_Contract_End_Date <= Date(Today()+30 and Sub_Contract_End_Date > Today()) , RED()))