Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
hi,
Try this if (Now()-Date#([Date raised])>7,rgb(255,0,0), rgb(255,255,255))
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.
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]