Help With Highlighting Cells Created The Last 7 Days

[pnn44794]

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.

[olivierrobin]

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

[pnn44794]

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?

[olivierrobin]

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

try to see the original value of created date

[pnn44794]

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.

[olivierrobin · Accepted solution]

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

[pnn44794]

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.

[pnn44794]

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.