Week Number of Month Filter

[jhill]

I am trying to figure out how to get the Week Number of the month so that I can use it as a dynamic filter. I know I can use WEEK() and get the week of the year. I have 5 tabs for each week of the month. Some months Wk 5 will be blank. What I am hoping to get is the week of the actual month. For example:

          June 2022

Week Number of Month First day of Week in month End of Week in month
1 06/01/2022 06/03/2022
2 06/06/2022 06/10/2022
3 06/13/2022 06/17/2022
4 06/20/2022 06/24/2022
5 06/26/2022 06/30/2022

The goals is to make a filter for each tab:

Wk 1 tab Filter =If week Number of month = 1 then filter tab to show only dates between 06/1 and 6/3
Wk 2 tab Filter =If week Number of month = 2 then filter tab to show only dates between 06/6 and 6/10
etc.

Any thoughts from the collective??

[nscheaffer]

I translated my solution directly from How To Get The Week Number Of Month In SQL Server?…

DECLARE @date_given datetime = ‘2022-06-02’;

SELECT (DATEPART(week, @date_given) -
DATEPART(week, DATEADD(day, 1, EOMONTH(@date_given, -1)))) + 1;

But this doesn’t depend on SQL Server in any way; it is all WebI. I created variable for each component of the calculation so I could understand it.

Var Week of Year=Week([Full Date])
Var Last Day Of Month=LastDayOfMonth([Full Date])
Var Last Day of Previous Month=RelativeDate([Var Last Day Of Month]; -1; MonthPeriod)
Var First Day of Month=RelativeDate([Var Last Day of Previous Month]; 1; DayPeriod)
Var First Week of Month in Year=Week([Var First Day of Month])
Var Week of Month=[Var Week of Year] - [Var First Week of Month in Year] + 1

Of course, now that I have verified everything is working I could combine all of that into one variable…

Var Week of Month All in One=Week([Full Date]) - Week(RelativeDate(RelativeDate(LastDayOfMonth([Full Date]); -1; MonthPeriod); 1; DayPeriod)) + 1

[MarkP]

My preferred option:
Do the work at the back end with a calendar table that has Week Number of Month precalculated for each date in the calendar. You can then use that as an object in your universe and process it as you see fit.

It means that you can apply the rules that you want rather than having to use any arbitrary rules that Webi applies to its week logic.

[nscheaffer]

I was pretty happy with my solution. However, just because you can doesn’t mean you should.

I like your approach better.

[dtolley]

Use the built in time dimension function in WebI.

Right click on your date/time object in the available objects panel. Select ‘Create Time Dimension’. Check the option for ‘Week’, rename if you want. Check the option to ‘Display additional attributes for time levels’. Adjust the First day of week, if applicable.

It will automatically create date/time details, including Week of Month.

[nscheaffer]

That is AMAZING! We just upgraded from 4.2 SP04 to 4.2 SP09 a few months ago. It seems like this feature was added in 4.2 SP06.

Thanks for the tip.

[MarkP]

That’s pretty cool. I’ve always brought in a separate data provider to give me a pseudo left outer join from the data range that I want to report against (e.g. all weeks in the current year)

To be able to do it straight out of the box is a great addition (albeit ten years overdue!)