How to highlight cell in a specific colour

[#1]

Hello,

I have read multiple threads about cell highlighting certain colours, but the issue I am facing is actually writing the expression. I have a table with technology names and dates of when the technology will start its retire lifecycle and when it ends.

Eg.

TechnologyName	LifecyclePhase	Retire_StartDate	Retire_EndDate
SAN Switch	Retire	19/12/2013	07/10/2014
PowerEdge	Retire	15/11/2013	02/02/2014
Tapes	Legacy	05/03/2014	01/04/2014
Windows Server	Retire	07/10/2014	07/10/2016

When the Retire_EndDate is within 70 days or less from today, I want the background of the cell to highlight red, otherwise it will stay green. I've achieved this on different platforms using something something along the lines of "DateDiff("day",Date(DateTimeNow()),[Retire_EndDate])<70" However Qlikview does not accept DateDiff.

Can anyone help with the expression in how to do this and incorporate colour?

Many thanks.

[#2]

Try something like....

=if((today()-[Retire_StartDate])<70,Red(),Green())

[jonathandienst]

Hi

Day differences are a simple subtraction:

=If(Today - Retire_EndDate < 70, LightRed(), LightGreen())

HTH

Jonathan

[MK_QSL]

Pls check enclosed file

[#5]

Hello Manish,

Unfortunately I cannot open the file as I am using Personal Edition. I thank everyone for their feedback but I'm still struggling in trying to get it to work. The steps I took:

Create Straight table > In Dimensions tab, under Used Dimensions I add Retire_EndDate > In Expressions tab, under Definitions I enter =if((today()-[Retire_EndDate])<70,LightRed(),LightGreen()) > Finish

The below is what is created, it just shows the html colour for red. Are you able to advise?

Kind regards, Thuy

[rajeshvaswani77]

Hi,

Put RGB in Background color.

thanks,

Rajesh Vaswani

[#7]

hi,

Under expression tab,

Write that rgb expression here.

hope this helps.

-Shruti

[#8]

Hello Rajesh,

Thanks for your prompt reply. So I have removed from Expressions tab under Definitions the code and replaced it with "Retire_EndDate". Then I added =if((today()-[Retire_EndDate])<70,LightRed(),LightGreen()) to Background Colour. Thithe below is what I see, all the coloums should not be red.

[#9]

Hi,

(today()-[Retire_EndDate]) will always give you -ve value.

Please try using ([Retire_EndDate]-today()) instead or use fabs.

Hope this helps.

-Shruti

[MK_QSL]

For color, you need to add your expression as per below.