Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

thonipad
Contributor III

WeekDay giving incorrect results

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.

4 Replies
disqr_rm
Valued Contributor III

WeekDay giving incorrect results

Wrap it with the num() function.

num(weekday(date_column))

thonipad
Contributor III

WeekDay giving incorrect results

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.

Not applicable

WeekDay giving incorrect results

Something like this:

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


Or
Valued Contributor II

WeekDay giving incorrect results

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)