Crystalkeen logo; we offer Crystal reports viewers and schedulers
 
Crystalkeen header image 3
Crystalkeen header image 5

Crystal Reports Tools: Improve Performance While Saving Time and Money

  Resources  

Articles:
Administration
Advanced
Basic
Database
Financial

Tools:

Analyzers
CR Schedulers
CR UFLs
CR Viewers
DataBase Tools
Graphics
International
Mail UFLs



 
 

Add'l:
About us

Contact Us
 

CrystalReports
on Steroids

Crystal Reports: Calculating the Week Number

In the Year | In a Formula

This article addresses how to calculate the week number, depending on what you want to accomplish.

Calculate the Week Number in the Year

A customer wanted to identify the week number of a dated transaction. This year January 1 is a Sunday, so it’s 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:

  • crUseSystem – use the system setting
  • crFirstJan1 – start with the week with January the 1st in it.
  • crFirstFourDays – start with the first week that has at least four days in the new year
  • crFirstFullWeek – start with the first complete week.

 

More, below....

 

How to calculate a Week Number using a Formula

What happens if your accounting year doesn’t start on the 1st 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 Jan-Mar

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 Jan-Jun

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}
(Saturday or Monday)

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 ‘ Sunday-go 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 ‘ Sunday-go 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.

Authorship

Except 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.

 

 

  • Questions? Please write to mark@crystalkeen.com. We do want your business.
  • Do you have your own tips for Crystal Reports administrators and designers? Write to mark@crystalkeen.com and we'll post your tips with a link to your Website (or with some other attribution if you choose).
  • Crystal Reports is a subsidiary of Business Objects, which is owned by SAP.