How to report by week - Thursday to Wednesday

building reports desktopintelligence
1

First post to the new forum… :smiley: hope someone finds this helpful

We recently had a requirement to report some amounts of money by week. However, it was necessary for the weeks to run from Thursday to the following Wednesday. Now there may be a really easy way to do this but I had to think a lot before I came up with this:

First create a variable with formula

= If (=“Thursday”) Then Else If (=“Friday”) Then -1 Else If (=“Saturday”) Then -2 Else If (=“Sunday”) Then -3 Else If (=“Monday”) Then -4 Else If (=“Tuesday”) Then -5 Else If (=“Wednesday”) Then -6

Then create a second variable based upon this called with formula

=FormatDate( ,“dd/mm/yyyy”) & " to " & FormatDate( ,“dd/mm/yyyy”)

Which gives you values along the lines of:

08/08/2002 to 14/08/2002 etc

Well I thought it was cool anyway… :crazy_face:

Nick Daniels :uk: (BOB member since 2002-08-15)

2

I’m impressed, Nick. That certainly IS a clever thing. :yesnod:

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

3

Welcome to Bob! :bob:

I looked at your “clever thing” and said to myself, “Self, there has to be a tricky way to use DayNumberOfWeek() to make this formula score even higher on the cool 8) scale.” I played with it for a bit, but don’t have anything yet. I’ll post back if / when I come up with something.

The idea would be to use the DayNumberOfWeek() to get the number of the day of the week, then “roll” it around so that Thursday is 0, and Wednesday is 6. Then you could simply take your call date and do:

<Call Date> - <Tricky Day Number of Week Thing>

… and make that your new items. Any thoughts?

Dave

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

4

Okay, here’s the formula:

<Call Date> - Mod(DayNumberOfWeek(<Call Date>) + 3, 7)

This takes any day number, adds 3, then does a “modulo” division to get the range you need. Try it out; let me know if it works! It’s not as obvious as your nice, clear “If” statement, but it’s cool… 8)

Dave

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

5

I’ll have to give this a try. I have user which has a report than runs on based on our Sales which run from Wed - Tue. This would be great so they could just have it scheduled weekly and not have to modify the dates. 8)

Stacy

Stacy Woodring (BOB member since 2002-08-15)

6

Thanks to Dave for tidying it up tho’…and obviously the same applies to any kind of rolling week/month/year/other unspecified period with a bit of thought.

Nick Daniels :uk: (BOB member since 2002-08-15)

7

Well, keep in mind that I also mentioned that your original version is more obvious for the next person working on the report. :wink: That is a definite consideration. Unless you don’t care about the next person working on the report… (joking) :rotf:

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

8

Hi Nick,

That is impressive, but I have a question about your second variable above. What is ? Is that another variable or formula? (I understood your example, but don’t understand how you obtain the Weds. based on previous Thurs.)

Thanks!
Judy

JMulders :us: (BOB member since 2002-06-20)

9

In Oracle, you can create objects like:
Last Wednesday: next_day(sysdate-7,‘WED’)
Following Thursday: next_day(sysdate,‘THURS’)

You can then base your range values to fall between these two… It’ll also happen dynamically.

-RM

digpen :us: (BOB member since 2002-08-15)

10

Hi Judy, the answer is +6

cheers, Nick

Nick Daniels :uk: (BOB member since 2002-08-15)

11

Another method of making your “Weeks” dynamic is to just group everything by weeknumber, shifting when the beginning of the week is. In your example you want to start with Thursday, wrapping to the following Wednesday.

You can create a “Week” object as follows:


trunc(( to_char( sysdate,‘J’) - to_char( next_day( to_date(12312001, ‘mmddyyyy’), ‘Thu’), ‘J’) ) / 7)

Basically, we calculate the first Thursday you are interested in, then convert it to a Julian date (to make it easier to perform math against it). Then we calculate the number of days between the current date (or your secondary date object) and the first Thursday. Divide that number by 7 and eliminate the decimals.

Works very well for creating a block of dates, and you can shift it by changing the next_day value.

To Convert the Week Number back to the First Date, just multiple this value by 7, add it to the Julian date of the First Thursday and then turn it back into a date value.

HINT: If you calculate the JULIAN date of the first Thursday, you can just hardcode that numeric value instead of the formula each time.

Hope that helps.

-RM

digpen :us: (BOB member since 2002-08-15)

12

Hi Nick,

Thanks for the clarification. I was wondering if my eyes are blurry from lack of caffeine! I thought you meant that you had defined it already.

Yes, I know about date addition in Oracle.

Cheers,
Judy

JMulders :us: (BOB member since 2002-06-20)

13

Can u tell me the same formular to convert Julian Date in Sql 2000

Thanks

jyothi (BOB member since 2002-08-28)

14

It’s not really a conversion, it’s more of a display or format for dates. I don’t have a SQL2000 book, but if you have one handy, see what types of date formats are available.

If I can track down a book (or a website), I’ll update the post to include it here.

-RM

digpen :us: (BOB member since 2002-08-15)

15

This seems to me to be one of those “cool Dave Tricks” that Cindy used to comment on… :+1:

Anita Craig :us: (BOB member since 2002-06-17)

16

Hi, Nick:

This is so cool. It works like charm.

I tried Dave’s trick as well, but the date column will display by rows, I am not sure how to do the concatenation for the Start Week Date and End Week Date to display data by week in a crosstab. I can do this using Nick’s formula.

Thanks.

Lilly

Lilly J (BOB member since 2002-08-22)

17

I was trying to do this in WebI and a quick search lead me to this post.

I spend a couple of hours to build the below forumula and wanted to post it just in case some one else is searching for this.
This will work for “Week” - Starts Thursday and Ends Wednesday.

=Floor((DayNumberOfYear([Date])+6+(Mod(DayNumberOfWeek(RelativeDate([Date];-1*DayNumberOfYear([Date])+1))+3;7)))/7)

bobj_user_a (BOB member since 2010-03-25)