topic Re: Date flags in Visualization and Usability

Date flags

davyqliks — Fri, 15 Nov 2024 22:35:07 GMT

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

Re: Date flags

chinmayadash07 — Thu, 16 Sep 2021 17:24:14 GMT

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

Re: Date flags

davyqliks — Fri, 17 Sep 2021 09:06:29 GMT

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

Re: Date flags

chinmayadash07 — Fri, 17 Sep 2021 13:41:25 GMT

Yes you are correct