Comparing Weeks By year

[bdmoran]

Hello,

I I am looking to compare Sales from the 2012-2013 season to the 2013-2014 season. Is there a formula I can create that matches week numbers from this year to the previous year?

I'm looking to set up a manual crosstab. Here is an example:

2012-2013 2013-2014
Group 1 = Month
October
-- Week 1 $3,000 $4,000
-- Week 2 $1,000 $2,000
-- Week 3 $6,000 $8,000
November
-- Week 1 $4,000 $2,000

and so on...

Pleae let me know if this makes sense. I appreciate any help!

 

[briangriffin]

This is a situation where having a date table really makes life easier. You could then group on week in year and create the manual crosstab as usual. Do you have the ability to create additional objects in your database, or any other database that is accessible from your reporting database?

 

[bdmoran]

Yes, I believe I could create a temp table in one of my databases in SQL Server. How would the joins work though?

 

[briangriffin]

Ideally this would not be a temp table.

Create a table with CalendarDate as the primary key. Add additional fields as needed - I usually have day of year (1-365), week of year (1-52), pay period start date, pay period end date, etc. You can always add more later. Script a loop that populates every day from a couple of years ago to five years from now, or whatever range suits your data. Let me know if you need a hand with that script.

Normally that would be the driving table in CR, with additional tables left joined to it on calendar date. Then you can add the week of year field to your query and group accordingly.

 

[bdmoran]

Alright so I created a table that has dates ranging from 1/1/2010-1/1/2050. I left joined my original table to my "Dates" table. Here are my groups:

Group 1. Formula = monthname(month({v_ticket_and_returns.add_datetime}))
Group 2. Date field from my "Dates" table
---Then I would sort this field ascending (by week) and create a datepart formula?

Am I heading in the right direction?

 

[briangriffin]

Add a column to the date table called WeekOfYear. Do an update:

update YourCalendarTable
set WeekOfYear = datepart(ww,CalendarDate)

So I think group 1 would be Month, and group 2 would be WeekOfYear, so you won't have to do any date manipulation within the report.

Then your manual crosstab would have formulas such as

if year(YourDataField) = 2012 then YourDollarAmount else 0

if year(YourDataField) = 2013 then YourDollarAmount else 0

Then I think you'll need to sum these formulas and hide the details to make the display look right.

BTW, the calendar table also solves the classic problem of "I want a record for every day even if there is no data".

 

[bdmoran]

Thank you for the response, Brian. My report is broken out by month, but not weeks. My second group is the WeekOfYear field but for some reason doesn't populate.

I will try and plug away at it. I apprecaite all your help.

Bests,
Brendon

 

[briangriffin]

I think it would depend on how you handle weeks that cross over months. But it should still work - for instance both 9/30/2013 and 10/1/2013 are in the 40th week of 2013 (at least as far as the SQL datepart function is concerned). But if your primary group is month, then those dates will still appear in the correct groups, and you can still compare them to the same week and month of prior years by grouping on week number.

 

[pmax9999]

Crystal also has the DatePart function that can return Week Numbers, and you get the ability to set what determines the "First week of the Year".

Couldn't you use that to get what you need?

Cheers
Pete

 

[bdmoran]

Pete - I tried using the DATEPART formula before I reached out for some help. However, it doesn't produce the results I am looking for.

 

[pmax9999]

Have your requirements changed from those in your original post?

Maybe if you explained what you did, the results, and how those results varied from what you wanted, we could assist further.

Pete

 

[bdmoran]

Alright so nothing changed from my original post -- but I can add on.

So with my first group being month, I added in the DATEPART function for my second group. I also have a third group which groups by team. (There are only 2 teams).

Group 1 = Month
Group2 = DATEPART(add_date)
Group 3 = Team

My problem is that the datepart function lists the dates in ascending order by year which makes it hard to read.

Example:
September
9/1/2012
9/8/2012
9/13/2012
9/1/2013
9/8/2013

Does this make sense? Note: This is without using Brian's Calender table he suggested.

Brendon

 

[pmax9999]

Not sure I understand. The DatePart function returns a number representing the week number, not a date. The formula should look like this:

DatePart('ww', {Table.YourDate})

Does this help?

Cheers
Pete

 

[bdmoran]

Yes, that is the formula I have been using. I shouldn't have used the "date" format in my last post, that was a bad example.

But basically what I was saying was it gives me repetitve numbers since I have dates in 2012 and 2013

So a better example is this:

September
Week 1
Week 2
Week 3
Week 4
Week 5
Week 6
Week 7

The weeks that are greater than 4 are the dates from Sept. 2013. However, I found an EASY solution to my problem.

Here is the formula I created and then used as my second group.

IF month({@AddDate}) = 8 AND (day({@AddDate}) > 0 and day({@AddDate}) <8 )THEN "Week 1" ELSE
IF month({@AddDate}) = 8 AND (day({@AddDate}) > 7 and day({@AddDate}) < 15 )THEN "Week 2" ELSE

I repeated this all the way until the month of march and it's been working well.

Thank you for all of your help Pete and Brian. Much appreciated !