topic Re: Help With Highlighting Cells Created The Last 7 Days in QlikView

Help With Highlighting Cells Created The Last 7 Days

pnn44794 — Sat, 12 Oct 2019 21:31:14 GMT

Hello:

I'm trying to highlight cells via the Background Color for Dimensions and Background Color for a couple of expressions to yellow if the order was created <= the last 7 days. I've been trying =If(Today() - [CREATED DATE] <= 7, Yellow()), but it's not working as expected.

Re: Help With Highlighting Cells Created The Last 7 Days

olivierrobin — Fri, 30 Mar 2018 15:51:23 GMT

hello

as usual in this case, verify the format of your date column

try to use date or date# function to be sure that it is comparable with the result of today()

try this

let a =today();

let b= [CREATED DATE];

trace $(a) $(b);

and see the result

Re: Help With Highlighting Cells Created The Last 7 Days

pnn44794 — Fri, 30 Mar 2018 16:58:10 GMT

Hello Olivier:

Thanks for the reply. I do use Date in the load script for CREATED DATE. I tried your suggestion, but I was having a hard time getting CREATED DATE to display until I remembered I do Date(CREATE_DATE, 'MM/DD/YYYY') as [CREATED DATE]. So, in tracing Today() and CREATE_DATE, I get 3/28/2018 for Today() and nothing for CREATE_DATE. I added a trace for Now() and I get

3/30/2018 12:55:21 PM. Shouldn't Today() have returned the same date as Now()? How do I show CREATE_DATE?

Re: Help With Highlighting Cells Created The Last 7 Days

olivierrobin — Fri, 30 Mar 2018 17:16:56 GMT

if date() applied to rated date returns nothing, thatmeans it not the correct format

try to see the original value of created date

Re: Help With Highlighting Cells Created The Last 7 Days

pnn44794 — Fri, 30 Mar 2018 17:28:21 GMT

I may have confused things. Sorry.

CREATE_DATE is loaded at load script run time as CREATED_DATE using Date(CREATE_DATE, 'MM/DD/YYYY') as [CREATED DATE]. Per your suggestion, I added the following lines of code on the first tab as one of the first items and on the last tab as the very last thing:

let a =today() & ' Todays Date';
let b= [CREATE_DATE] & ' Is Created Date';
let c =now() & ' Is Nows Date'; //<--I added Now() to see if it gave a different result than Today()
trace $(a) $(b) $(c);

today() returns a date of 3/28/2018 which is wrong, I believe. It should be 3/30/2018.

CREATE_DATE does not return anything. Maybe that's because it hasn't been loaded yet????

now() returns today's date with a timestamp as expected.

Once the reload is complete, I can see that CREATED_DATE is loaded and in the MM/DD/YYYY format in the straight table I have it displayed in.

Re: Help With Highlighting Cells Created The Last 7 Days

olivierrobin — Fri, 30 Mar 2018 17:36:29 GMT

have a look at the qlik documentation on various options of today and see what it gives

Re: Help With Highlighting Cells Created The Last 7 Days

pnn44794 — Fri, 30 Mar 2018 18:10:18 GMT

Ok. So it looks like I need to use Today(1) as that returns today's date. Changing my expression to =If(Today(1) - Date([CREATED DATE], 'MM/DD/YYYY') < 8, Yellow()) still does not highlight the cells in yellow.

Re: Help With Highlighting Cells Created The Last 7 Days

pnn44794 — Fri, 30 Mar 2018 18:18:18 GMT

Ok. This is embarrassing. I got it. Might help if I include CREATED DATE as a Dimension in the straight table. I added it and now cells are highlighting as expected.

Thank you very much for your patience and the pointers. I did still learn something.