How to select a non-standard week

Fri, 10 Aug 2012 16:22:25 GMT

Hello all,

We have a user request to look at a "weeks" worth of data but defining a week as A “Week” is considered Sunday @ 5:00:00am to next Sunday @ 04:59:59am.

We have a timestamp field "PROCESSED DATE" that shows the date and time of each transaction.

How can I handle this? I know that if they wanted the regular calendar week it would be easy enough, but how can I tell Qlikview to go from 7/29/2012 5:00:00 AM to 8/4/2012 4:59:59am?

Thanks

Re: How to select a non-standard week

swuehl — Fri, 10 Aug 2012 18:01:33 GMT

I believe you should be able to this using weekstart() function with optional shift / weekoffset arguments set.

Well, trying this, I couldn't get it to work, seems to me that the weekoffset doesn't really like fractions of a day

{maybe I am missing something here or made a silly mistake}.

But a workaround using an additional daystart() seems to work:

LOAD *,

weekname(Weekstart) as Weekname;

LOAD *,

weekday(DateTime) as Weekday,

timestamp(WeekStart(Daystart(DateTime,0,maketime(5)),1,-1)) as Weekstart;

LOAD

Timestamp(makedate(2012)+rand()*100) as DateTime

AutoGenerate 1000;

This code should assign all timestamps within your required intervals a common weekstart timestamp, from which you can derive e.g. a weekname. Use this weekname (or the Weekstart timestamp) to group your records.

Hope this helps,

Stefan

edit: corrected a typo

topic How to select a non-standard week in QlikView

How to select a non-standard week

Re: How to select a non-standard week