Counting weeks in a month

building reports desktopintelligence
1

Good morning,

I am trying to count the weeks in a month in my Bus. Obj. report. Does anyone have any suggestions on how that can be achieved?

Thanks,
Daintysally

daintysally (BOB member since 2005-12-20)

2

The recommended approach is to use a smart date calendar table in the database / universe. There are so many different ways to define week (start on Sunday or Monday … what if Monday is a holiday … and so forth). In the calendar table, populate a column with “week number” based on your company’s definition. Then it’s a simple matter to count the number of unique week numbers within month.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

3

Thank you for your input. I have never used/created a smart date calendar table. Can you give further input on how to do this?

Thanks Again.

daintysally (BOB member since 2005-12-20)

4

As Dwayne suggested having it done in the universe is an excellent solution, if that’s available to you. If not, can you define what a “week” is and how you want it counted? In other words, is a week simply 7 days? Or is it a period of 7 days from Sun - Sat? Do you want only full weeks (those where the complete Sun - Sat is contained within the month)? Or are partial weeks okay?

There might be a formula that can be done in the report, but the process needs to be defined first. :slight_smile:

Dave Rathbun :us: (BOB member since 2002-06-06)

5

Thank you both for the excellent guidance.

A week is being defined simply as seven days. I do have the universe available to me, but I would prefer that it be done at the report level.

daintysally (BOB member since 2005-12-20)

6

You know the current date with the CurrentDate() function. You can get the number of days in the month with a combination of LastDayOfMonth() and DayNumberOfMonth. So this will give you the number of “seven day periods” in a month:

=Floor(DayNumberOfMonth(LastDayOfMonth(CurrentDate())) / 7)

Find the number of days in a month, divide by 7, throw away the remainder. That will give you the number of “complete” 7 day periods in a month. If you want fractions, don’t use the Floor() command.

Does that get you what you want?

Dave Rathbun :us: (BOB member since 2002-06-06)

7

Because of my lack of knowledge of the data, I apologize for saying that a week was as simple as 7 days. By report is set up by month based off of the fiscalweek.

This is the breakout out of weeks according to our fiscal calendar starting with August:

Aug: 8/1 - 8/28 (4 wks)
Sep: 8/29 - 10/2 (5 wks)
Oct: 10/3 - 10/30 (4 wks)
Nov: 10/31 - 11/27 (4 wks)
Dec: 11/28 - 12/31 (5 wks)

In my report, I am wanting to show a count of the fiscal weeks in a month. :?

daintysally (BOB member since 2005-12-20)

8

If you have a fiscal calendar, I assume you have a fiscal calendar table then. That would be your best bet using the strategy suggested earlier.

Dave Rathbun :us: (BOB member since 2002-06-06)