topic Re: Qlikview Date Color Change setup in QlikView

Qlikview Date Color Change setup

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

Hi,

I am trying to set a back ground color for a date to turn yellow if its within two months but once the due date is past then I want it to turn white. I used the expression below but it stays yellow even if the date is past due.

For instance already past days are still yellow. I inserted the expression in the background color definition.

if([Contract Renewal Date]<=today()+ 60, Yellow(), White())

Thank you

Re: Qlikview Date Color Change setup

Gysbert_Wassenaar — Mon, 04 Jun 2018 18:15:34 GMT

Make sure the field [Contract Renewal Date] actually contains date values and not just strings that only look like dates. You can use the date# function in the script to turn string values into date values. Feel free to post a small example demonstrating the problem if you get stuck.

Re: Qlikview Date Color Change setup

taylor_jesse — Mon, 04 Jun 2018 18:39:37 GMT

Hi,

I added date() to the contract renewal date field but still getting the same result

I did

Date([Contract Renewal Date]) as [Contract Renewal Date].

Do you think maybe something needs to be aded to the expression so that when the date passes the yellow background color turns off?

Thank you

Re: Qlikview Date Color Change setup

Gysbert_Wassenaar — Tue, 05 Jun 2018 07:41:48 GMT

The date() function only applies a format to a value. The date#() function can turn text values into date values.

Re: Qlikview Date Color Change setup

taylor_jesse — Tue, 05 Jun 2018 13:26:17 GMT

Good morning,

Changing adding date#() removed all the values in the cell. Do you think an expression can be written to make this change?

Thank you

Re: Qlikview Date Color Change setup

Gysbert_Wassenaar — Tue, 05 Jun 2018 15:13:45 GMT

Have you read the online help about the date#() function?

If not, have a look at https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/InterpretationFunctions/date_hash.…‌

Of course, it could be another problem altogether. Can you post a small example qlikview document that shows the problem?

Re: Qlikview Date Color Change setup

taylor_jesse — Tue, 05 Jun 2018 15:36:43 GMT

Please see the image below. The first date with yellow is now a past date and the background color still remains.

Re: Qlikview Date Color Change setup

agomes1971 — Tue, 05 Jun 2018 15:43:36 GMT

Hi,

=if(rangesum(before( YourExpression , 0, columno() )) = 0, yellow() )

HTH

André Gomes

Re: Qlikview Date Color Change setup

taylor_jesse — Tue, 05 Jun 2018 15:46:25 GMT

Hi,

Do you mean column() or columno?

Thank you

Re: Qlikview Date Color Change setup

agomes1971 — Tue, 05 Jun 2018 15:47:55 GMT

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/InterRecordFunctions/columnno…

Re: Qlikview Date Color Change setup

kfoudhaily — Tue, 05 Jun 2018 16:05:39 GMT

you should use the fonction date#(field,'dd/mm/yyyy')

to check for the difference please go to the following link: Date() vs Date#()

Re: Qlikview Date Color Change setup

taylor_jesse — Tue, 05 Jun 2018 18:45:16 GMT

When I use that function in the script, no values show at all in the straight table I did

date#([Contract Renewal Date],'dd/mm/yyyy') as [Contract Renewal Date]

Re: Qlikview Date Color Change setup

taylor_jesse — Tue, 05 Jun 2018 19:25:55 GMT

if(rangesum(before(if([Contract Renewal Date]<=(today()+60),0,ColumnNo()))=0,yellow())

Everything turned yellow

Re: Qlikview Date Color Change setup

agomes1971 — Wed, 06 Jun 2018 09:54:05 GMT

Hi,

please share a small sample.

Thanks

André Gomes

Re: Qlikview Date Color Change setup

taylor_jesse — Wed, 06 Jun 2018 13:13:36 GMT

How do I share a sample without sharing the whole document?

Re: Qlikview Date Color Change setup

agomes1971 — Wed, 06 Jun 2018 13:16:25 GMT

Hi,

just share a excel with little and scrambled data.

HTH

André Gomes

Re: Qlikview Date Color Change setup

taylor_jesse — Wed, 06 Jun 2018 13:42:25 GMT

Unable to attach any file just images, videos and links.

Re: Qlikview Date Color Change setup

agomes1971 — Wed, 06 Jun 2018 13:45:01 GMT

Make reply go to advanced editor!

Re: Qlikview Date Color Change setup

taylor_jesse — Wed, 06 Jun 2018 13:49:19 GMT

Please see the attached file. I will like to make the background color yellow for contract renewal date that are 6 months away but once the date is passed then it should turn back to white.

Thank you

Re: Qlikview Date Color Change setup

agomes1971 — Wed, 06 Jun 2018 14:03:53 GMT

Hi,

use this expression:

=if([Contract Renewal Date] > addmonths (Today(),6), LightRed(), White())

HTH

André Gomes