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?
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.
