topic Re: Help with expression with dates. in QlikView

Help with expression with dates.

Fri, 16 Jan 2015 13:33:23 GMT

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!

Re: Help with expression with dates.

Gysbert_Wassenaar — Fri, 16 Jan 2015 13:39:28 GMT

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 ...

Re: Help with expression with dates.

Fri, 16 Jan 2015 13:41:21 GMT

Thank you!

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

date(Today)

Greetings

Re: Help with expression with dates.

Gysbert_Wassenaar — Fri, 16 Jan 2015 13:44:13 GMT

Yes, you can use today()

Re: Help with expression with dates.

Fri, 16 Jan 2015 13:58:08 GMT

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

Re: Help with expression with dates.

Gysbert_Wassenaar — Fri, 16 Jan 2015 13:59:57 GMT

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.

Re: Help with expression with dates.

Fri, 16 Jan 2015 14:03:31 GMT

Ok now it's working

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

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

Re: Help with expression with dates.

Fri, 16 Jan 2015 14:11:59 GMT

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