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: 
gerhardl
Creator II
Creator II

Start weeks on Sundays

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

10 Replies
Not applicable

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

gerhardl
Creator II
Creator II
Author

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.

Not applicable

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

swuehl
MVP
MVP

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

gerhardl
Creator II
Creator II
Author

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

gerhardl
Creator II
Creator II
Author

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.

G

swuehl
MVP
MVP

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.

jagan
Luminary Alumni
Luminary Alumni

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.

gerhardl
Creator II
Creator II
Author

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