
Crystal Reports Tools: Improve Performance While Saving Time and Money 

Crystal Reports: Calculating the Week NumberThis article addresses how to calculate the week number, depending on what you want to accomplish. Calculate the Week Number in the YearA customer wanted to identify the week number of a dated transaction. This year January 1 is a Sunday, so its quite easy to calculate the days since Jan one and divide by 7. But what if you wanted the matching week number in a different year? The "DatePart" function has some really powerful features here. DatePart("ww",{Table.DateField},crSunday) returns the week number and assumes the week starts on a Sunday. Use different day of the week constants (crMonday, crTuesday, etc) if your weeks start on a different day. If you omit the day of week constant then Crystal assumes Sunday is the start of the week. A variation on this is to use First Week of Year constants as in DatePart("ww",{Table.DateField},crFirstJan1). This sets the rule for when the first week of the year is calculated. Valid values are:
More, below.... 
How to calculate a Week Number using a FormulaWhat happens if your accounting year doesnt start on the 1^{st} of January? In New Zealand, most companies start their financial year on 01 April. We know of several companies who start their year on 1 July. In New Zealand government, departments often use a July to June financial year. Part One: Calculate the start of the financial year {@FY Start} The first formula you need is to calculate the start of the financial year based on a date field in your transaction table. Here is a formula for an April to March Financial Year. If month({table.datefield}) <= 3 then // Transaction is in JanMar Date(year({table.datefield}) 1 , 4,1) Else Date(year({table.datefield}) , 4,1) If your year starts in July, use this variation of the formula. If month({table.datefield}) <= 6 then // Transaction is in JanJun Date(year({table.datefield}) 1 , 7,1) Else Date(year({table.datefield}) , 7,1) Part Two: Decide when the first week starts {@Week Start} Now we need to calculate when the first week starts. This example calculates the first Saturday of the financial year, and then assumes the first incomplete week is Week One. You can adjust this if your week starts on another day or if Week One is calculated differently see later examples. If DayofWeek({@FY Start}) =1 then {@FY Start} //Already Starts on a Sunday Else {@FY Start}  DayofWeek({FY Start}) + 1 Part Three: Calculate when the transaction week starts {@Transaction Week} Now we need to calculate the when the week of the transaction record starts. This example calculates the prior Sunday of each record. If DayofWeek({table.datefield}) =1 then {table.datefield} // Already Sunday Else { table.datefield }  DayofWeek({table.datefield }) + 1 Part Four: Calculate the week number All you need to do now is to subtract those two numbers. They will both always refer to a Sunday, so therefore will always be a multiple of seven days apart. ({@Transaction Week} {@Week Start}) / 7 ^{ }
Part Five: Decide when the first week starts {@Week Start} The Crystal Reports default working week starts on a Sunday, and all of the previous sample formulas assume that day. However, there could be a good reason to start the week on a Saturday or a Monday. The first formula from last month {@FY Start}, calculated the start of the financial year. It needs no changes and works as required. Now we need to calculate the when the first week starts on a Saturday. This example calculates the first Saturday of the financial year, and then assumes the first incomplete week is Week One. If DayofWeek({@FY Start}) =7 then {@FY Start} Already a Saturday Else {@FY Start}  DayofWeek({@FY Start}) If your week starts on a Monday, use this variation of the formula. If DayofWeek({@FY Start}) =1 then {@FY Start}6 Sundaygo back Else If DayofWeek({@FY Start}) =2 then {@FY Start} Already a Monday Else {@FY Start}  DayofWeek({FY Start}) + 2 Calculate when the transaction week starts {@Transaction Week} Use a similar formula to calculate the start of the week for the transaction date. Use this formula for the Saturday start of week. If DayofWeek({table.datefield}) =7 then {table.datefield} Already a Saturday Else {table.datefield}  DayofWeek({table.datefield}) And use this one for the Monday week based formula If DayofWeek({table.datefield}) =1 then {table.datefield}6 Sundaygo back Else If DayofWeek({table.datefield}) =2 then {table.datefield} Already a Monday Else {table.datefield}  DayofWeek({table.datefield}) + 2 The formula to calculate the week number is the same as last month. Just subtract those two dates. They will both always refer to a Sunday, so therefore will always be a multiple of 7 days apart. ({@Transaction Week} {@Week Start}) / 7
See also: 
This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd.
It may be freely copied and distributed as long as the
original copyright is displayed and no modifications are made to this
material. Extracts are permitted. The names Crystal Reports and Seagate
Info are trademarks owned by Business Objects.
AuthorshipExcept where an author's name is given at the start of the article, all of these articles were written by Mo Naughton or Bruce Ferguson and edited by Mark Lamendola. Mo is a Crystal Reports consultant, trainer, and developer for Chelsea Technologies, Inc. Bruce Ferguson is a Crystal Reports consultant, trainer, and developer for CrystalKiwi, Inc. Mark Lamendola is a writer and editor with over 15 years experience in professional and trade publications. 

