topic Week Ending in QlikView

Week Ending

Mon, 22 Feb 2010 15:11:46 GMT

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

Week Ending

Mon, 22 Feb 2010 15:17:55 GMT

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'

Week Ending

Mon, 22 Feb 2010 15:47:24 GMT

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

Week Ending

Mon, 22 Feb 2010 16:36:23 GMT

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

Week Ending

Mon, 22 Feb 2010 16:53:48 GMT

Excellent, thanks very much for your help with this Lukas