Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Yes you are correct