topic Re: How do I write this IF Statement for Days Late? in QlikView

How do I write this IF Statement for Days Late?

Sun, 25 Jan 2015 21:19:10 GMT

Hello there,

I am trying to write an IF statement for number of days a Job is late.

This is my sample data below:

This is the rule I am trying to write:

The Days Late should be calculated in numbers.

If Resolved_Date is greater than Requested_Date calculate the difference.

If Requested Date is blank, calculate Days_Late using Date Due

If both are blank insert 'Unresolved'.

Can anyone share an example of this? Sorry I am new to writing if statement with dates

Re: How do I write this IF Statement for Days Late?

Sun, 25 Jan 2015 21:34:13 GMT

Try this

If(isnull(Requested_Date)=0,Resolved_Date-Requested_Date,If(isnull(Due_Date)=0,Resolved_Date-Due_Date,'UnResolved'))

This expression will provide a negative number if the Resolved date is before the requested date.

Re: How do I write this IF Statement for Days Late?

Sun, 25 Jan 2015 21:51:14 GMT

Hi Aushik,

Thank you for your reply

Does the =0 mean when it finds a null it place a 0 on true for null?

Re: How do I write this IF Statement for Days Late?

maxgro — Sun, 25 Jan 2015 21:52:51 GMT

you can use

if(len(trim(field)) > 0, ..., ....)

to check if field is null, empty

date1 - date2

to calc the difference between date

if(date1 > date2, ..., ....)

to compare dates

Re: How do I write this IF Statement for Days Late?

Sun, 25 Jan 2015 21:55:04 GMT

Hi,

No, here isnull(dimension) returns true or false. So if the column is null it returns -1 and if value is not null it returns 0.

you may also use simply

if(isnull(field), dim1, dim2)...this will be the reverse of checking 'isnull(field)=0'

Re: How do I write this IF Statement for Days Late?

Sun, 25 Jan 2015 22:16:38 GMT

Thanks Aushik,

I forgot to add one more criteria, if both Date_Requested and Due_Date are blank then it should calculate the difference between Resolve_Date and Created_Date.

Any idea how I can add this in?

Re: How do I write this IF Statement for Days Late?

Sun, 25 Jan 2015 22:45:52 GMT

Hi,

You may do that by including another if condition nested inside the above example.

However the most optimal way would be to define a new field in the reload script as below

If(isnull(Requested_Date),If(isnull(Due_Date),Created_Date,Due_Date),Requested_Date) as Target_Date

Then in the UI you can simply use Resolved_Date-Target_Date

Re: How do I write this IF Statement for Days Late?

Sun, 25 Jan 2015 23:20:47 GMT

Hi Aushik,

Does the Target Date search for values in the sequential order you have in the If Statement?

Re: How do I write this IF Statement for Days Late?

Mon, 26 Jan 2015 19:28:12 GMT

Yes...first it checks the Requested_Date. If Requested_Date is null it checks the 'Due_Date' If both of these dates are null, it uses the created date.