Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gerhardl
Creator II
Creator II

New calculated time field

Hi, I have two fields, [Log Date] and [Decision Date], loaded as follows:

Date(Date#( left( [Log Date],9), 'DD-MMM-YY')) as [Log Date],
Date(Date#( [Decision Date], 'DD-MMM-YY')) as [Decision Date],

I would like to create a new field called [Process Time], which subtracts the Log Date from the Decision Date and returns me the number of DAYS.

So if Log Date is 18-APR-2011 and Decision Date is 20-APR-2011 the Process Time must be "2 days".

BUT if the application was logged and finalised on the SAME date (process time = 0 days), it must display as "Same Day"

Thanks,

G

1 Solution

Accepted Solutions
Not applicable

try this


if(date#([Log Date],'DD-MMMM-YYYY') =date#([Decision Date],'DD-MMMM-YYYY'), 'Same Day', date#([Log Date],'DD-MMMM-YYYY') -date#([Decision Date],'DD-MMMM-YYYY')) as [Process Time],



View solution in original post

13 Replies
chris_johnson
Creator III
Creator III

Hi,

Something like:

if([Decision Date]=[Log Date],'Same Day', if([Decision Date]-[LogDate] = 1, '1 day', [Decision Date]-[Log Date] & 'days')) as [Process TIme]

I have not tried this myself though!

Regards,

Chris

Not applicable

try

if(logdate-decisiondate=0, 'Same', logdate-decisiondate) as process time

Not applicable

is this working for you? I made little example

t:
LOAD * INLINE [
F1, F2
20-March-2009, 15-March-2009
];

load *, F1 - F2 as diff resident t;

drop table t;

Seams it's not working.

martinpohl
Partner - Master
Partner - Master

Hello,

change your example to

load *, date#(F1,'DD-MMMM-YYYY') - date#(F2,'DD-MMMM-YYYY') as diff resident t;

Regards

gerhardl
Creator II
Creator II
Author

It's not working yet, but it's due to our IT department being silly so I havge different date formats, and some are even timestamps and not just dates.

I have

Date(Date#( left( [Log Date],11), 'DD-MMM-YYYY')) as [Log Date],
Date(Date#( [Decision Date], 'DD-MMM-YYYY')) as [Decision Date],

Wil try the other suggestions now...

gerhardl
Creator II
Creator II
Author

Nope...

I now have

Date(Date#( left( [Log Date],11), 'DD-MMM-YYYY')) as [Log Date],
Date(Date#( [Decision Date], 'DD-MMM-YYYY')) as [Decision Date],

if([Log Date]-[Decision Date]=0, 'Same Day', [Log Date]-[Decision Date]) as [Process Time],

It loads fine but process time field is blank

gerhardl
Creator II
Creator II
Author

I now change the expression to the following to try and track down the problem:

if([Log Date]-[Decision Date]=0, 'Same Day', 'No') as [Process Time],

And they are all now "No". So this means that the two fields (even though the dates are the same for most) are not seen as the same. This must be due to the fact that one is a time stamp ('DD-MMM-YYYY HH:MM:SS') and the other just a date ('DD-MMM-YYYY').

?

Not applicable

try this


if(date#([Log Date],'DD-MMMM-YYYY') =date#([Decision Date],'DD-MMMM-YYYY'), 'Same Day', date#([Log Date],'DD-MMMM-YYYY') -date#([Decision Date],'DD-MMMM-YYYY')) as [Process Time],



gerhardl
Creator II
Creator II
Author

This is driving me crazy.

I tried the above and got an error (remember it's MMM not MMMM). I played around with it a bit, but there are too many damn ('s and )'s I can't get it right!

At the moment I have

if(Date#( left( [Log Date],11), 'DD-MMM-YYYY')) =date#([Decision Date],'DD-MMM-YYYY'), 'Same Day', Date#( left( [Log Date],11)), 'DD-MMM-YYYY' -date#([Decision Date],'DD-MMM-YYYY') as [Process Time],

But the error tells me "if takes 2-3 parameters"