topic Re: Week Start - Sunday instead of Monday in QlikView

Week Start - Sunday instead of Monday

Thu, 03 May 2012 07:58:59 GMT

Is there any option in QlikView to set the week start globally to desired Day instead of default Monday.

I have tried using -1 in WeekStart but there are lot of problems.

Please check these out

LET vCYDate = Date(Today());

LET vCYWeekStart = WeekStart(vCYDate,0,-1);

LET vCYWeekNo = Week(vCYWeekStart);

LET vPYWeekDay = MakeWeekDate(Year(vCYDate)-1,vCYWeekNo, vCYWeekDay);

LET vPYWeekStart = WeekStart(vPYWeekDay,0,-1);

LET vPYWeekNo = Week(vPYWeekStart);

The main thing is I want to get the same week details for 2 given years. Try for Year 2012 and 2011 in the way I have written the code. Except first week of Jan, the result is giving the correct result. Is there a way to overcome this?

Try using WEEKNUM(<Date>) in Excel for both these dates (Jan 1st 2012 and Jan 1st 2011) as well in QlikView.

Actually the WeekStart shows Sunday but the Week number of that date is showing 1 week less than the correct week # as the vCYWeekStart / vPYWeekStart is considered as the last day of the previous week in QlikView.

How to globally set in QV to make Sunday as the start of the week and the rest of the date calculations follow it?

Week Start - Sunday instead of Monday

SunilChauhan — Thu, 03 May 2012 08:40:44 GMT

at the start of edit script while defineing varibles

you can change the order of

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Sun;Mon;Tue;Wed;Thu;Fri;Sat';

instead of SETDayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

and save application

hope this helps

Re: Week Start - Sunday instead of Monday

Thu, 03 May 2012 08:44:33 GMT

SET DayNames='Sun;Mon;Tue;Wed;Thu;Fri;Sat';

is similar to SET DayNames='q;w;e;r;t;y;u';

It is just to customize the language and not the real start of the week

I tried this and it shows April 30th 2012 as Sunday though it is Monday

Re: Week Start - Sunday instead of Monday

martinpohl — Thu, 03 May 2012 09:57:58 GMT

Not very nice but:

Just added one day for your date.

So in your case:

instead of week for 2012/04/29 is 17.

Regards

Re: Week Start - Sunday instead of Monday

Tue, 08 May 2012 08:54:44 GMT

Atlast I got fedup with QV's inbuild date functions and wrote a VBScript to get the previous year's week date

Function GetPYWeekDate(dDate)

ctYear = Datepart("yyyy",dDate)

ctDayofYear = Datepart("y",dDate)

cyFirstDay = Datepart("w",datevalue("1/1/" & ctYear))

pyFirstDay = Datepart("w",datevalue("1/1/" & ctYear - 1))

GetPYWeekDate = dateadd("d", - (pyFirstDay - cyFirstDay) + ctDayofYear - 1, datevalue("1/1/" & ctYear - 1))

End Function

If anyone needs this, use it

Week Start - Sunday instead of Monday

hic — Tue, 08 May 2012 09:20:38 GMT

It's easier if you do it in the script instead of in VBS. Leave the DayNames variable as it is and use

Dual(Subfield('$(DayNames)',';',WeekDay(Date)+1),Mod(Date-1,7)) as MyWeekDay

HIC

Re: Week Start - Sunday instead of Monday

Tue, 08 May 2012 11:00:59 GMT

Hi Henric,

Thanks for your input. It is returning 1 day more than the QV's normal weekday

Today() : 5/8/2012 (Tuesday)

WeekDay: Num(WeekDay(Today()),'#') : 1

MyWeekDay: Num(Dual(Subfield('$(DayNames)',';',WeekDay(Today())+1),Mod(Today()-1,7)),'#') : 2

WeekStart(Today()): 5/7/2012

WeekStart(Today(),0,-1): 5/6/2012

If its Tuesday, it should return either 2(Weekday) / 3(MyWeekDay), but it is returning 1 / 2. Does this mean that Weekday starts with zero?

My function will return the foll

GetPYWeekDate(Today()) = 5/3/2011

How to achieve the above using only QV script instead of VBS?

We can easily subtract a day to get the week's start day as Sunday even without using these many conversions, but Week function will not return the correct Week # for the StartDate of the Week

Is there something like AddDays similar to AddMonths & AddYears? I think that may resolve this issue. I don't want to use "+ <days>" to add days

My basic requirement is I want to set the start of the week to be Sunday instead of Monday permanently, and not temporary (still I haven't got the answer).

If that is the case, then only the current year's week number and previous year's week number are tallying, else it is not.

1/1/2012 - Weekstart should be 1 instead of 7 (1st week) - in QV, this is the last week's last day of previous year

and 1/1/2011 - Weekstart should be 7 instead of 6 (prev year's last week) - So 1st week of year 2011 starts on 2nd Jan and not on 3rd

If the above condition satisfies, then only we can get the answer for proper Week over Year value

Re: Week Start - Sunday instead of Monday

hic — Tue, 08 May 2012 11:25:15 GMT

Both the built-in WeekDay and my function start at 0 and go to 6. With this definition WeekDay for today should be 1 (second day of week) and my function should be 2 (third day of week). You can check this yourself by formatting a listbox with WeekDay as a number (Properties - Number).

But for my function you can easily change this if you want an other value: The Dual function takes two parameters - the second defines the number. So, just change Mod(Date-1,7) to Mod(Date-1,7)+1. It will still work using Sunday as the first day of the week.

The function WeekStart does not change, so it will still return Monday as the first day of the week. (ISO 8601; http://en.wikipedia.org/wiki/ISO_week_date)

HIC

Re: Week Start - Sunday instead of Monday

Tue, 08 May 2012 11:30:35 GMT

=MakeDate(Year(vCYStartDate)-1,1,1)

- (

Dual(Subfield('$(DayNames)',';',WeekDay(WeekDay(MakeDate(Year(vCYStartDate)-1,1,1)))+1),

Mod(WeekDay(MakeDate(Year(vCYStartDate)-1,1,1))-1,7))

Dual(Subfield('$(DayNames)',';',WeekDay(WeekDay(MakeDate(Year(vCYStartDate),1,1)))+1),

Mod(WeekDay(MakeDate(Year(vCYStartDate),1,1))-1,7))

)

+ Dual(DayNumberOfYear(vCYStartDate)) - 1

Is this the way to achieve previous year's weekday using your MyWeekDay?

Re: Week Start - Sunday instead of Monday

hic — Tue, 08 May 2012 12:57:45 GMT

I am not sure if I understand what you are trying to do... If it is just a matter of getting the weekday of previous years day, then you can use "WeekDay(AddMonths(Date, -12))" or with the alternative Weekday function around the AddMonths function instead.

The Dual function should normally be the outermost function:

Dual(

<Text that you want to display>,

)

Btw, there is no function AddDays because you do not need one. You should indeed just use "Date( Date+<days>)".

HIC

Re: Week Start - Sunday instead of Monday

Tue, 08 May 2012 13:07:12 GMT

If we use AddMonths or AddYears, it will give exactly the same date rather than the same day of previous year

For example, if today is Tuesday, 8th May 2012, I don't want 8th May 2011 which is Sunday

8th May 2012 is Week 19 and Day 3 (Sunday being the first day)

8th May 2011 is Week 20 and Day 1

So my result should be Day 3 of 19th week of 2011 (May 3rd 2011) and not the same date of 2011 (May 8th 2011)

Re: Week Start - Sunday instead of Monday

hic — Fri, 11 May 2012 10:15:59 GMT

If you use the attached script, then you can calculate week number and week day so that Jan 1st always belongs to week 1 and Sunday always is the first day of the week. Then you can compare year-to-year numbers using WeekNumber and WeekDay: (Note that the difference from previous year is calculated also)

Should you want to calculate the date of the previous year's corresponding date in the script, you can do this also. See attachment.

HIC

Re: Week Start - Sunday instead of Monday

Fri, 11 May 2012 12:35:46 GMT

Thanks a lot...

Re: Week Start - Sunday instead of Monday

Wed, 19 Dec 2012 17:06:55 GMT

2012 starts on week 2, but so far i can't figure out a fix to your code for this.

Re: Week Start - Sunday instead of Monday

Sun, 28 Sep 2014 14:11:02 GMT

Hello @ Henric Cronström,

This piece of code is indeed useful, but I want to know how to tweak this to suit my case.

Here the US Week 1 is Jan 1 - Jan 4 2014 - where we want to have the Dec 29th to Jan 4 2014 as Week 53 of 2013 instead.

The definition of our week is that a Week Starts Sunday and should have 7 days, in the above calendar has a week which is less than 7 days - so in case when I have to compare Week 1 of Jan 2014 to Week 2 to Jan 2014 I end up comparing 4 days to 7 days.

Any thoughts ?

Thanks,

Lav