topic Re: Start weeks on Sundays in QlikView

Start weeks on Sundays

gerhardl — Wed, 14 Dec 2011 11:25:53 GMT

Hi there,

There are a few discussions on this topic, but I was not yet able to apply the proposed solutions to my application.

I pull data from different extracts where our dates are in this formatL DD-MMM-YY.

So I usually start with this:

DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')) as [Effective Date]

Then I do the following to get the week:

if(week(DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')))='21','01] 27/06-03/07' as [Effective Week]

The company I do these reports for start their financial year in July, so this is seen as their First Week of the year.

However, their weeks start on Sundays, not Mondays. So their actial first week is 26/06-02/07 (The 26th being a Sunday).

How do I get the weeks in QV to start on Sundays? So I need week 21 to be 26/06 to 02/07, not 27/06 to 03/07.

Please assist.

Thank you,

Gerhard

Start weeks on Sundays

Wed, 14 Dec 2011 11:32:50 GMT

Hi,

By default qlikview take start week on monday,by using weekstart function u can shift weekstart date on sunday

ex Weekstart('2011-06-27',0,-1)

Thanks

VIvek

Start weeks on Sundays

gerhardl — Wed, 14 Dec 2011 11:49:07 GMT

Hi Vivek,

I have tried this but cannot get it to work. Would you mind putting this in context with the way my load statements looks now:

DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')) as [Effective Date],

if(week(DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')))='21','01] 26/06-02/07' as [Effective Week],

At the moment week 21 starts on Monday 27 July. I need week 21 to start on Sunday 26 July.

Start weeks on Sundays

Wed, 14 Dec 2011 12:12:13 GMT

Hi,

DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')) as [Effective Date],

if(week(DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')))='21','01]' &weekstart(DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')),0,-1)&'-'&weekend(DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')),0,-1)) as [Effective Week],

I hope this will help

Thanks

Vivek

Re: Start weeks on Sundays

swuehl — Wed, 14 Dec 2011 13:12:51 GMT

I think there is a potential issue in using the week = 21 comparison:

Doing so, I believe this will not assign the correct Effective Week value to Sunday, 26 June, because this Effective Date is in week 25 (I assume we are talking about year 2011, but anyway, it will never be week 21).

[edit: I think I missed my point here: Using the week function, you will not get the same week returned for Sunday 26, June and the following days to July 2nd, so you won't assign the same Effective Week to Sunday, so you are not getting what you intend to get]

Then, if you are not doing this for one special year only, I would be cautious to compare to a fix week number, this might shift +-1 over years.

Regards,

Stefan

Start weeks on Sundays

gerhardl — Wed, 14 Dec 2011 13:48:45 GMT

Hi Stefan,

Yes it's week 25 sorry. But you are correct and this did cross my mind - soon it will be 2012 and then this solution won't work anyway..

Do you ahve any suggestions?

My starting point for many fields will just be one date. In this case the field is [Effective Date] in the DD-MMM-YY format. From these fields I must create day names and months (which are easy), and weeks.

This will for instance be the week in which a transaction happened or an account was opened or an application was received, etc. I always have to create a week based on the day, because the clients want to see results weekly, starting on Sundays.

I appreciate the input.

Regards,

Gerhard

Start weeks on Sundays

gerhardl — Wed, 14 Dec 2011 15:24:13 GMT

Okay I figured it out (I think).

I am now adding the following:

Date(weekstart( DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')))-1)&' - '& Date(weekend( DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')))-1) as Eff_Week_Start,

Then I use Eff_Week_Start as dimension to split all my tables. This way it will report on weeks from Sunday to Saturday, even though they are now no longer numbered from 1 to 52.

Thanks for both of your inputs, they helped a lot.

Start weeks on Sundays

swuehl — Wed, 14 Dec 2011 15:40:23 GMT

That's probably a way to do this (though I wouldn't call it Week_Start if I would add also the weekend date to the string, but that's only a side note).

If you want to assign a week number, you could probably just do a

week( weekend(DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')),0,-1) ) as WeekNumber

This week number may not be correct in a certain calendar system (e.g. US) defining certain rules when to start week 1 in a year, though you might want to check this.

Start weeks on Sundays

jagan — Thu, 15 Dec 2011 13:12:15 GMT

Hi,

Try this to set Start day of week to Sun

Change this in script Main tab.

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

Regards,

Jagan.

Re: Start weeks on Sundays

gerhardl — Thu, 15 Dec 2011 13:22:01 GMT

Hi Jagan,

I have tried this before and it did not work for me. I did manage to figure this out though – see my above comment.

Thanks,

Gerhard Laubscher

Tenacity (a Pepkor company)

P.O. Box 6387, Parow East, 7501, South Africa

T: +27 (0)21 928 1017

F: +27 (0)21 928 1012

C: +27 (0)82 080 4190

www.tenacityinc.co.za<http://www.tenacityinc.co.za/>

gerhardl@tenacityinc.co.za<mailto:stephnik@tenacityinc.co.za

Re: Start weeks on Sundays

Wed, 02 May 2012 14:17:13 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

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);

Try using WEEKNUM(<Date>) in Excel for both these dates.

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?