Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
have a look at the qlik documentation on various options of today and see what it gives
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
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?
if date() applied to rated date returns nothing, thatmeans it not the correct format
try to see the original value of created date
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.
have a look at the qlik documentation on various options of today and see what it gives
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.
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.