topic Re: Daystart function not working as expected in QlikView

Daystart function not working as expected

Wed, 20 Mar 2013 18:26:37 GMT

Further to my issue with trying to calculate the time between a lead being received, and it being processed, I had to devise a way to deal with zealous Admins who came in before 9 am to catch up on their work. I thought this would work:

AdjustedLeadDate = if(hour(IncLeadDate)<9,daystart(IncLeadDate,0,.375),IncLeadDate)

This would set all the incoming leads that came in early to 9:00 AM, and those that came in after 9 AM to their actual time.

However, when I put this in my report, I would get the following results:

IncLeadDate AdjustedLeadDate

3/15/13 8:38 AM 3/14/13 9:00 AM <-- Note the date is adjusted to previous day, even though shift is zero

So I thought maybe the documentation was incorrect, and I had to put in daystart(IncLeadDate,1,.375). But when I did that,

I got:

IncLeadDate AdjustedLeadDate

3/15/13 8:38 AM 3/16/13 9:00 AM <-- Note the date is adjusted to next day.

Any thoughts on this?

Kevin

Re: Daystart function not working as expected

Gysbert_Wassenaar — Wed, 20 Mar 2013 18:37:23 GMT

As I understand it if you set the start of the day to 9:00 AM then times before that day fall in the previous day. The daystart of such datetimes would be 9:00 AM the previous day. That doesn't explain why you get 3/16/9:00 AM if you use an offset of 1 day. I haven't been able to reproduce that here.

Alternatively you could try:

if(hour(IncLeadDate)<9,floor(IncLeadDate) + 0.375,IncLeadDate)

Re: Daystart function not working as expected

Wed, 20 Mar 2013 18:47:42 GMT

Thanks, that worked well. (using floor(IncLeadDate)+.375)

Still don't understand, though - there's nothing in the documentation that says "daystart()" considers times before 9 am to be the previous day, and the function works fine when I'm using it to adjust leads that come in on Saturday or Sunday to calculate as if the lead came in Monday at 9 am.

But problem solved, so thanks again!