Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

gerhardl
Contributor 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

Tags (1)
1 Solution

Accepted Solutions
Not applicable

New calculated time field

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],



13 Replies
chris_johnson
Contributor II

New calculated time field

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

New calculated time field

try

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

Not applicable

New calculated time field

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
Valued Contributor II

AW:Re: New calculated time field

Hello,

change your example to

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

Regards

gerhardl
Contributor II

New calculated time field

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
Contributor II

New calculated time field

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
Contributor II

New calculated time field

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:MMSmiley FrustratedS') and the other just a date ('DD-MMM-YYYY').

?

Not applicable

New calculated time field

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
Contributor II

New calculated time field

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"