Days of the week defaults (Changing first day of week to Sunday)

[#1]

Hello QV gurus. It appears that the first day of the week in QlikView is Monday (Weekday for Monday = 0). Is there a setting where this can be changed to match that of the U.S. weekday values, where the first day of the week is Sunday? Thanks.

[johnw]

There's no single setting that affects everything in the entire document from script to charts. What there are are a lot of different solutions depending on exactly what you're trying to do. Some functions allow you to specify the start date for the week. Taking a random example, you can weekstart(date,0,-1). The final parameter is a week offset, telling it to offset your week by one day back from the default. Other functions, like the week() function, don't allow you to specify an offset, an annoying inconsistency. But I have solutions for what I think are the most common definitions of week number, including some US week number options.

So what exactly are you trying to do?

[#3]

Thanks, John. Based your number of points, I take it you're on the expert level. I consider myself a newbie when scripting in QlikView (less than one year), but I enjoy learning it very much. I develop in ABAP/SAP. I was working on a report to report plant efficiency going back a rolling 12 weeks (90 days or so). I wanted to report from Sunday to Saturday, on each week. We measure our weeks starting on Sunday, as most US companies probably do, and our data is sparced accordingly. I just found it somewhat confusing working with Monday start, day 0. Regardless of which day of the week, the report runs on, it must determine the previous 12 weeks in full weeks, no partials. I coded around that using day 6 (Sunday), as determining start day. We have a graph that shows our efficiency by week, so I imagine that although we are looking at the report under the assumption the week starts on Sunday, QlikView is actually reporting as of Monday. Might be acceptable in weekly buckets, or larger.

Will the command you mentioned (weekstart(date,0,-1)), change that default for the duration of the job runtime? If so, what exactly would the code look like in the script? Thanks again for your help and input.

[johnw]

Are the rolling 12 weeks based on the date the QlikView was most recently loaded? Can a user choose a different date, like pretending that today was May 4, 2008, and get a rolling 12 weeks based on that date?

So let's say we load on (or the user chooses) Wednesday, May 18, 2011. You want to exclude the week of Sunday, May 15 through Wednesday, May 18 because it is a partial week. You want the 12 weeks prior to that, so... it looks like you want data between Sunday, February 20 and Saturday, May 14, 2011? And you want a report, by week (Sunday through Saturday), of efficiency?

I'll assume the simpler case to start with - the rolling 12 weeks is based on the load date, and is not user-selectable. I'd have a Calendar table separate from my data, and depending on how I wanted to display the data, I might add a Rolling12Week or Rolling12WeeksBack field like this (I probably wouldn't have all these fields, but I wanted to show how it was built, and give you some options):

[Calendar]:
LOAD *
,if(WeeksBack>=1 and WeeksBack <=12,Week) as Rolling12Week
,if(WeeksBack>=1 and WeeksBack <=12,WeeksBack) as Rolling12WeeksBack
;
LOAD *
, weekstart(Date,0,-1) as Week
,(weekstart(today(),0,-1) - weekstart(Date,0,-1))/7 as WeeksBack
;
LOAD
date(makedate(2009,12,31)+recno()) as Date
AUTOGENERATE today()-makedate(2009,12,31) // in a real application, this would be based on your data
;

So the weekstart(Date,0,-1) function is what gives you Sunday through Saturday. But it only does this on a field by field, use by use basis. There's no global override going on, and Monday through Sunday weeks can happily coexist with Sunday through Saturday weeks in the same application (though I didn't do that in this example). Then we create a Rolling12Week field that is only established for those specific weeks, which lets you create the chart by using that dimension instead of Week. Suppress when value is null, and only the rolling 12 weeks are shown on the chart.

See attached.

[#5]

Thanks, John. That certainly looks workable. I will work with that code. Thanks again for all your help. This will help be lots.

[Anonymous]

Hi - I'm actually trying to change my default days of the week using the same "weekstart" formula. I'm loading week, year, month etc via the load script using transact sql (datepart). What I'd like to do is have some trending etc for multiple charts on a Tuesday to Monday work week. Is there anyway to do this without specifying a calculated dimension as shown below and do it in the load script? This way I avoid repeating the dimension calculation over and over again in each chart.

Thanks very much!

Simon

Dimension:

=

Week(weekstart([StartTime],0,1))

Load Script:

Scan_Date = DATEPART(yyyy, s.StartTime),

Scan_Month = DATEPART(mm, s.StartTime),

Scan_Week = DATEPART(ww, s.StartTime),

Scan_Day = DATEPART(dd, s.StartTime),

[johnw]

Maybe I'm not understanding. If you're happy with the week numbers that week(weekstart([StartTime],0,1)) is producing, just put that in your script instead of in a chart.

week(weekstart([StartTime],0,1)) as Week

[Anonymous]

Thanks John! I think you put me on the right path now. I'm now using that function in resident load following my transact sql. However, when I select the Week from a list box - my data sets(charts etc) are not changing.

Any hints or ideas here, I'm sure it something simple but I think I may have fried my brain working on this for so long 😉

Cheers,

Simon

[johnw]

Well, it's hard for me to know what you're doing, but here's an example of something that I think technically matches what you just said, and would fail.

Data:
SQL SELECT
StartTime
,Blah
,Something
FROM SomeTable
;
MyWeeks:
LOAD week(weekstart([StartTime],0,1)) as Week
RESIDENT Data
;

That fails because there's no connection in your data model between Week and [StartTime], plus you have a million duplicate copies of Week. No idea if that's what you're doing, though. What we typically do for problems like this is create a Calendar table. Is StartTime a timestamp? There's more here than is necessary to just get the Week to work, but to show how I'd likely handle it personally (untested):

Data:
LOAD
date(daystart([StartTime])) as [StartDate]
;
LOAD
timestamp(timestamp#([StartTime],'your database format here')) as [StartTime]
,[Blah]
,[Something];
SQL SELECT
StartTime
,Blah
,Something
FROM SomeTable
;

Dates:
LOAD
min([StartDate]) as MinDate
,max([StartDate]) as MaxDate
;
LOAD date(fieldvalue('StartDate',iterno())) as [StartDate]
AUTOGENERATE 1
WHILE len(fieldvalue('StartDate',iterno()))
;
LET vMinDate = num(peek('MinDate'));
LET vMaxDate = num(peek('MaxDate'));

DROP TABLE [Dates];

Calendar:
LOAD *
,week(weekstart([StartTime],0,1)) as [StartWeek]
,...various other date-related fields here...;
LOAD date($(vMinDate)+recno()-1) as [StartDate]
AUTOGENERATE $(vMaxDate) - $(vMinDate) + 1
;

When done, you have a calendar table that connects the StartDate to the StartWeek. There are much simpler ways to solve your specific problem, but this is how I'd do it, and I can explain my reasons for each bit of it if necessary.

[hugo_andrade]

Very helpful