Need Help with Week number calculations - Week 1 vs week 53

[Former Member]

I am writing a crystal report that is a weekly sales comparison spanning 3 years. The report will compare the same week #'s sales for each of the 3 years. My problem is on week 1 and week 53.

I am using the formula: DatePart ("ww",{view_Posted_GL_MultiCo.TRXDATE},crSunday,crFirstJan1 ). I want the week to start on a Sunday, and the year to start with the week that has January 1 in it. Using this formula if I have a week that January 1 is on a Monday, I want December 31 to be week 1. This formula is returning week 53.

I am using a parameter for Week ending date and week number. That is the first year, then I want to compare the data of the two prior years of the same week.

I appreciate any help.

Thank you,

Rochelle

[Former Member]

I grouped off of Right Part_of_Julian 1st and

then grouped off of Left Part_of_Julian

Then grouped off date_print_area.Date (By Week)

I clicked on group name fields and added the group 3 "Group by date_print_area.date" into the details

( I created a dummy table in Excel, with all the dates from 1/1/2011 - 12/31/2013)

and used that to create the report.

[Former Member]

V361-V361,

I appreciate your responses, and you are returning the results that I am desiring. I have created the Julian date field, and the left and right segments. They each return the "real" julian date. I've done the three groupings also. I regret that I am still not returning the same results as you.

How are you using the excel spreadsheet? Are you using it as a translator?

I will be on vacation the next two days and will be unable to respond to any posts. Thank you in advance.

[Former Member]

Hi Rochelle, the excel is a data source only, all the magic is being done in crystal. I am using CR XI

You could right click on the Group Name Fields in the Field explorer.

I assume your group # 3 is the group by date

You can click on that group, and drag it to your details line. You should see the weekly date in the details section.

If not, I may need to email you my report example.

[Former Member]

I have put in your calculations for the Julian Date. However, I'm not getting the same results as you. How you are getting 12/26/10 to return 11001? My formula is returning 10360. I'm missing the step of how you made it realize 12/26/10 is the first day of the year.

Regards

[Former Member]

Well, this may not work for you either, but try this... I created a formula to convert the date to Julian

totext(year({DATE_Print_Area.Date}),"00") & totext(datediff('d',date(year({DATE_Print_Area.Date}),01,01),{DATE_Print_Area.Date})+1,"000");

// where X is the date field and assuming that the year is two digits, otherwise

totext(year({DATE_Print_Area.Date}),0,'','')[3 to 4] & totext(datediff('d',date(year({DATE_Print_Area.Date}),01,01),{DATE_Print_Area.Date})+1,"000");

I called it Julian_date

Just replace {DATE_Print_Area.Date} with your date field

I then created two more formulas, one to pull the left most part of the Julian_date

the other to pull the right 3 characters of Julian_date

I called them (formula is between \ \ )

Left Part_of_Julian \ left({@Julian_date},2) \

Right Part_of_Julian \ rightt({@Julian_date},2) \

I then created grouping, I grouped off of Rightt Part_of_Julian 1st and

then grouped off of Left Part_of_Julian

Then grouped off date_print_area.Date (By Week)

I hope this is what you were looking for.

This is what my details line looks like

Julian Left_Part_of_Julian Right Part_of_Julian Week

11001 11 001 12/26/2010

12001 12 001 01/01/2012

13001 13 001 12/30/2012

11002 11 002 01/02/2011

12002 12 002 01/01/2012

13002 13 002 12/30/2012

[Former Member]

Hi,

I used Excel and set up 1/1/2011 to 12/31/2011 and when I use your formula, Saturday Jan 1st is week one.

Sunday Jan 2nd is week two.

I changed your formula

DatePart ("ww",,crSunday,crFirstfullweek )

and that resolved the issue.

Hopefully this will be ok with the other years involved, I tested with 2012, and all was ok until the very last few days

of December.

[Former Member]

you have to watch for the year

the 01/01/20?? could be week1 year 20??or week 53 year 20??-1 depending on the start of week1

therefore if you must caclulate year ( the year function on a date will no work)

is this your issue?

[Former Member]

Thank you for responding

It could be the year that is confusing me. In my select statement, I have a statement that defines the date range for each year, for example, 2011 is from 12/26/10 to 12/31/11.

I agree with what you are saying about how the date 01/01/20?? could be week1 year 20??or week 53 year 20??-. However in the report I am writing I can not double dip on a date. It either has to be week 1 or week 53. If 12/31/xx is in week 1 in year xy I can not have it beweek 1 in xx.

I thought by using DatePart ("ww",{view_Posted_GL_MultiCo.TRXDATE},crSunday,crFirstJan1 ) I am essentially saying The week always starts on a sunday and the week that contains January 1 is week 1.

Do I need to add a year statement in addition to this?