The original poster wanted the first seven days of each month, regardless of what days of the week they were, to be considered the first week of the month. That is what ceil(day(date)/7) does. It works fine IF that's your definition of the week of the month.
If it only works for you when the month starts on a Sunday, then you have a DIFFERENT requirement than the original poster. YOU want your weeks to start on Sunday. That's fine. It's just a completely different problem and requires a completely different solution.
Converting your SQL to QlikView, you get week(date)-week(monthstart(date))+1. However, QlikView weeks start on Monday, not on Sunday. That makes things more complicated. I believe that the QlikView week() function implements the ISO 8601 standard for weeks. According to this standard, weeks start on Monday, and the first week of the year includes January 4. My understanding is that this is the standard in most of Europe:
,week(Date) as "ISO Week"
I work at a company in the United States, and we use a modified version of the ISO 8601. In our version, weeks start on Sunday instead of Monday, but the first week of the year still includes January 4. I don't actually use week numbers in any of my applications, but I believe that the expression to implement our week numbers would be this:
,if(date(weekend(Date,0,-1),'MMMM D')='January 3'
,53
,week(Date+1)+(date(weekend(yearstart(weekstart(Date,0,-1)),0,-1),'MMMM D')='January 3')) as "ISO Week but Start Sunday"
However, my understanding is that most of the United States uses a different standard than even this. I believe that the normal United States standard is that weeks start on Sunday, and the first week of the year includes January 1. I believe that can be coded like this:
,1-(year(weekend(Date,0,-1))=year(Date))*ceil((Date-floor(weekend(yearstart(Date),0,-1)))/7) as "US Week"
But any of these standards mean that the first week of the year may start in the previous year, and some allow the last week of the previous year to end in this year. Some companies prefer to split the week apart if it crosses the year boundary so that weeks within a year are always ascending, and never go back to 1 at the end. So weeks usually start on Sunday, except for the first week of the year which starts on January 1. Weeks normally end on Saturday, except for the last week of the year which ends on December 31. The first and last week of each year may be less than 7 days. I believe that can be coded like this:
,1+ceil((Date-floor(weekend(yearstart(Date),0,-1)))/7) as "US Week but Start and End in Year"
The only one of these definitions that would allow you to subtract the week of the start of the month from the week of the current date is the very last one. All of the others could return negative numbers for the week number. So I'm going to guess that you want the "US Week but Start and End in Year". But you don't need to use that as your week function and calculate both the week at the beginning of the month and the week of the current day. Instead, you can pretty much apply the same expression to the month as we were applying to the year. So what I think you're looking for, assuming you don't want to do it in SQL, is this QlikView expression:
,1+ceil((Date-floor(weekend(monthstart(Date),0,-1)))/7) as "US Week but Start and End in Month"
Attached file demonstrates all of these for about fifteen years of data.
