Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Issues with date based formatting

Hi,

I'm trying to set up a basic straight table chart, that lists service desk tickets and highlights ones that are past the agreed SLA time based on a priority. So for example a priority 2 should be highlighted if it's older than 2 weeks.

For basic testing I set an expression up with the following on the background colour property using the addmonths function -

if ([Date raised] < addmonths(NOW(),-1),rgb(255,0,0), rgb(255,255,255))
However there's only addmonths and addyears, and I need to work on day and week values. I tried using the age function by adding a new column with the age in days (I figured I could then use this to drive the formatting) but oddly this only seems to work on certain rows of data, others just don't get a value. The expression is

(Date(Now()) -  Date([Date raised]),'D')

So for example my table looks like this -

Ticket number     Date raised          Age
1                         05/02/2014          498
2                         17/06/2014          -

3                         22/09/2014          -

4                         10/01/2014          524


I can't see any difference between the rows that work and those that don't. Does anyone know either an easier way to do this, and/or why the age doesn't seem to work properly?

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Check the default date format of your document. If that format is MM/DD/YYYY then 05/02/2014 is be interpreted as May 2nd 2015. But there are only 12 months so 17/06/2014 can't be interpreted as a valid date. Your Date field might contain both valid dates and text values. So your Age column would get a null value if there's no valid date.


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
Not applicable
Author

hi,

Try this if (Now()-Date#([Date raised])>7,rgb(255,0,0), rgb(255,255,255))

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Check the default date format of your document. If that format is MM/DD/YYYY then 05/02/2014 is be interpreted as May 2nd 2015. But there are only 12 months so 17/06/2014 can't be interpreted as a valid date. Your Date field might contain both valid dates and text values. So your Age column would get a null value if there's no valid date.


talk is cheap, supply exceeds demand
Not applicable
Author

Thanks, I edited my data load in my script to force the date format and then all the values came back in the table -

date(num([Date raised]),'DD/MM/YYYY') as [Date raised]