topic Re: Help with script in QlikView

Help with script

hopkinsc — Tue, 11 Sep 2012 16:24:48 GMT

Hi All

I am trying to work out how long a call is open for. The working day is between 9 -5 Mon-Fri. If a call is opened on a saturday then qlikview should report it as being opened on the Monday. and if the call is closed on the weekend then qlikview should report it as being closed on friday end of day. If any calls are open and closed on weekday then qlikview should report the actual day.

This is what i have used for my start/end times..

 timestamp(LastWorkDate([Create DateTime],1,$(vBankHols))+$(vWeekDayStartTime)) as [Create WorkingDateTime],  // roll forward to Monday SoD (*1)
 timestamp(FirstWorkDate([Closed DateTime],1,$(vBankHols))+$(vWeekDayEndTime)) as [Closed WorkingDateTime],  // roll backward to Friday EoD (*1)

The problem i am getting is that my new field [Create WorkingDateTime] displays ALL original call open times as 9am. I only want it to be 9am (The following Monday) if opened on a weekend.

Does anyone have any ideas?

Re: Help with script

hopkinsc — Tue, 11 Sep 2012 19:35:22 GMT

hi all, anyone have any ideas?

Re: Help with script

Tue, 11 Sep 2012 21:11:28 GMT

Hi hopkins,

the LastWorkDate(Date, 1) returns in case of Workdays the Day itself, otherwise the next Workday. In both cases your are adding the timeoffset "$(vWeekDayStartTime)".

So try this modification of your expression:

Timestamp(LastWorkDate([Create DateTime],1) + if(weekDay([Create DateTime])>4, $(vWeekDayStartTime),0)

HtH

Roland

Re: Help with script

hopkinsc — Tue, 11 Sep 2012 21:29:54 GMT

Hi, thanks for that, it seems to work if you select a weekend but if you select a week day or no day at all (So all days are selected) it returns 00:00:00 as the time..

The time needs ti stay as the original time logged if logged on Mon-Fri 9-5.

Thanks

Chris

Re: Help with script

hopkinsc — Wed, 12 Sep 2012 07:30:48 GMT

Any more help with this anyone?