topic WeekDay giving incorrect results in QlikView

WeekDay giving incorrect results

Anonymous — Sat, 19 Jun 2010 18:46:04 GMT

I understand that weeday(date_column) should give values 1 thru 5. But It gives me Mon, Tues....Sun. How can I get the same result as Oracle function TO_CHAR(date_column,'W')?

Thanks,

Dinesh.

WeekDay giving incorrect results

disqr_rm — Sat, 19 Jun 2010 19:24:21 GMT

Wrap it with the num() function.

num(weekday(date_column))

WeekDay giving incorrect results

Anonymous — Mon, 21 Jun 2010 15:16:06 GMT

What I was looking for is the Week of Month. The Week function gives Week of the Year. How do I achieve this? Same as Oracle TO_CHAR(DATE,'W').

Thanks,

Dinesh.

WeekDay giving incorrect results

Mon, 21 Jun 2010 15:31:23 GMT

Something like this:

= floor(day(date#('6/30/2010')) / 7)

WeekDay giving incorrect results

Or — Mon, 21 Jun 2010 15:40:56 GMT

As far as I know, there's no way to achieve this using QlikView's available function (I wasn't even aware you could do it in Oracle until I read your post). Some alternatives might include:

1) Write a custom function (such as the one suggested in the post above mine, which was added while I was writing this)

2) Get the WeekInMonth value via database query

3) Create a flat file / Excel sheet with a list of dates and their responding WeekInMonth values (this can be auto-generated easily), add it to your load script, and join with your existing data (We use this solution for customized week numbers and working day counts)