Help with expression with dates.

[#1]

Hi. I have "end date". Im trying to do a graph to show if the insurance policy have expired, if is close to the expirement date, and if not.

I think this should be like

Actual date - "end date" = negative number or 0 -- > Expired

Actual date - "end date" = 1 to 30 days --> near to expirement date

Actual date - "end date" = 30 + days --> Ok.

What should i do ?

Thank you

Greetings!

[Gysbert_Wassenaar · Accepted solution]

Create an extra field in the script

LOAD

     Policy,

     ...other fields...,

     [Actual Date],

     [End Date]

     if([Actual Date] - [End Date] <0, 'Expired',

          if([Actual Date] - [End Date] < 31, 'Close to expire', 'Ok')) as Status

FROM ...

[#3]

Hi

Thank you!

I don't have actual date, for example can i use

date(Today)

?

Greetings

[Gysbert_Wassenaar]

Yes, you can use today()

[#5]

I dont know why this isn't working

if(today() - end_date<0, 'Expired',

if(today() - end_date < 31, '1month', 'Ok')) as Status,

today()

and i have for example this

end date 15/02/2015 today 16/01/2015 status = expired

it may be because the dates aren't negative?

Greetings

[Gysbert_Wassenaar]

More likely your end dates aren't dates but text values. If so you'll have to use the date# function to create dates from those text values.

[#7]

Ok now it's working

if(date([enddate]) - today()  <0, 'Expirada',

if(date([enddate]) - today() < 31, '1 Mes de Expirar', 'Ok')) as Status,

[#8]

One question. Can i change the color

if is ok is red, and if is expired es green, in a table.

I tried with visual efects but it didn't work

thank you