Week Ending

[#1]

Hi all,

I am looking at creating a week ending date using a date field named "BinArrived" - can anyone help? I would like to use Friday as the week ending point for this - does anyone know the QlikView SQL that would do this?

Thanks in advance,

Chris

[#2]

Hi Chris,

would use WeekEnd(BinArrived)-2 (to get from Sunday to Friday)

Below is from the help:

WeekEnd( date [, shift = 0 [,weekoffset = 0]] )

Returns a value corresponding to a timestamp with the last millisecond of the last date ( Sunday ) of the calendar week containing date The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the week which contains date. Negative values in shift indicate preceding weeks and positive values indicate succeeding weeks. If you want to work with weeks not starting midnight between Sunday and Monday, you may indicate an offset in days in weekoffset. This may be given as a real number indicating days and/or fractions of a day.

Example:

weekend ( '2006-01-12' ) returns '2006-01-15' with an underlying numeric value corresponding to '2006-01-15 23:59:59.999'

weekend ( '2006-01-12', -1 ) returns '2006-01-08' with an underlying numeric value corresponding to '2006-01-08 23:59:59.999'

weekend ( '2006-01-12', 0, 1 ) returns '2006-01-16' with an underlying numeric value corresponding to '2006-01-16 23:59:59.999'

[#3]

Excellent - many thanks for this.

I am assuming that to get the week commencing date then I would do WeekEnd(BinArrived),0,1 as per the help to move the date to Monday, or is their a more efficient (or best practice) way of getting the week commencing date (using Monday as the week commencing)

Thanks,

Chris

[#4]

Hi Chris,

glad it works for you. For W/C I would use (as a matter of personal preference simply WeekEnd(BinArrived)+1.

Cheers, lukas

[#5]

Excellent, thanks very much for your help with this Lukas