Date flags

[davyqliks]

Hi

I have a date field and would like to highlight when the date is older than today 'expired' or with 3 months until today 'due'

I have the following but i am not getting the correct results.

if(cert_expiry<= Today(), 'Expired') as AuditExpired,
if(cert_expiry < today() +90, 'Due') as AuditDue,

as the backgound colour measure i use the following:

=If([AuditExpired]='Expired' ,RGB(188, 46, 39),
If([AuditDue] ='Due' ,RGB(244, 192, 37)
))

but when i look at the attached below i see some dates are not correctly coloured

I am hoping one the the experts can assist on why some are coloured and some not.

Thank you in advance

Daniel

[chinmayadash07 · Accepted solution]

My suggestion just check the date format first for cert_expiry if its matching with Today() Format, once that is done please check the formula, instead of creating two field create a single column

if(cert_expiry< Today(), 'Expired'  ,
if(cert_expiry > =today() +90, 'Due','Ontime')) as Expired_DueFlag

in your expression you are giving todays date as both expired as well as due. i believe for due it needs be today+90 days, that says , in next 3 months from today  , which comes under due. Please correct me if my understanding is correct

[davyqliks]

Thankyou for the reply @chinmayadash07 

May i just confirm the Today() function uses the Date variable in the load?

SET DateFormat='DD/MM/YYYY';

thanks 

[chinmayadash07]

Yes you are correct