topic Re: How to highlight cell in a specific colour in QlikView

How to highlight cell in a specific colour

Wed, 08 Jan 2014 13:28:04 GMT

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.

Re: How to highlight cell in a specific colour

Wed, 08 Jan 2014 13:34:13 GMT

Try something like....

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

Re: How to highlight cell in a specific colour

jonathandienst — Wed, 08 Jan 2014 13:34:28 GMT

Day differences are a simple subtraction:

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

HTH

Jonathan

Re: How to highlight cell in a specific colour

MK_QSL — Wed, 08 Jan 2014 13:38:50 GMT

Pls check enclosed file

Re: How to highlight cell in a specific colour

Wed, 15 Jan 2014 10:05:35 GMT

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

Re: How to highlight cell in a specific colour

rajeshvaswani77 — Wed, 15 Jan 2014 10:09:50 GMT

Hi,

Put RGB in Background color.

thanks,

Rajesh Vaswani

Re: How to highlight cell in a specific colour

Wed, 15 Jan 2014 10:16:37 GMT

hi,

Under expression tab,

Write that rgb expression here.

hope this helps.

-Shruti

Re: How to highlight cell in a specific colour

Wed, 15 Jan 2014 10:17:47 GMT

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.

Re: How to highlight cell in a specific colour

Wed, 15 Jan 2014 10:25:03 GMT

Hi,

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

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

Hope this helps.

-Shruti

Re: Re: How to highlight cell in a specific colour

MK_QSL — Wed, 15 Jan 2014 10:25:32 GMT

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

Re: Re: How to highlight cell in a specific colour

Wed, 15 Jan 2014 10:34:12 GMT

Thank you everyone for your feedback. I managed to get the colour working as expected. Really grateful!

Kind regards, Thuy

Re: How to highlight cell in a specific colour

kiranmanoharrode — Wed, 15 Jan 2014 10:48:26 GMT

Hi Thuy,

Use following expression in background of Retire_EndDate Dimension. this will solve.

=if(Num(Today())-Num(Date(Retire_EndDate,'DD/MM/YYYY')>'70',Red(),Green())

Regards,
Kiran Rode

+91 8976977897