topic Use previous date when comparing shift data in QlikView

Use previous date when comparing shift data

Fri, 10 Jun 2011 08:47:30 GMT

I am trying to look at the activity of employees who work shifts which span across midnight each day. so if they work up to 4 am that is classed as the previous day.

I have two fields

Date

Time

In the script I want to add a new field Activity date.

I have tried

IF('00:00:00' < Time > '03:59:00', (date-1), date) as " Activity Date",

but this doesn't work, am sure it's a simple syntax thing?

Many thanks

Matt

Use previous date when comparing shift data

pat_agen — Fri, 10 Jun 2011 09:00:29 GMT

hi Matt,

if Date is a timestamp then this would do

if(hour(Date)<4,date-1,date) as ActivityDate

if Time a character string like this '00:00:00' ? then try

if(subfield(Time,1)<4,date-1,date) as ActivityDate

hope thsi helps

Use previous date when comparing shift data

Fri, 10 Jun 2011 09:40:52 GMT

Many thansk Pat that seams to do the trick

Matt

Use previous date when comparing shift data

pat_agen — Fri, 10 Jun 2011 09:45:13 GMT

and you thought you wre the one with syntax probelms