Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Week Start - Sunday instead of Monday

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?

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

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)

US_WeekNumber.png

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

View solution in original post

14 Replies
SunilChauhan
Champion
Champion

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

Sunil Chauhan
Not applicable
Author

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

martinpohl
Partner - Master
Partner - Master

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

Not applicable
Author

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

hic
Former Employee
Former Employee

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

Not applicable
Author

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

hic
Former Employee
Former Employee

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

Not applicable
Author

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

hic
Former Employee
Former Employee

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>,

      <Number that is a unique identifier for the day/date>

      )

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

HIC