Calculating 13 weeks prior to a date.

I have a payroll report with a period end date.  I want to display this date and 13 weeks of payroll data from this date to show 13 payroll weeks of data.  How or what method is best to calculate weeks in Crystal Reports?
garyjgsAsked:
Who is Participating?
 
Kurt ReinhardtConnect With a Mentor Sr. Business Intelligence Consultant/ArchitectCommented:
Your question is a little broad.  Are you asking how to filter the report for the specified date range or how to format the report once you have data (1 column, per payroll week, for example), or both?

For filtering data, you might be able to use some variation of :

{table.date} in DateAdd("ww", -13, {?StartDate}) to {?StartDate}

For formatting, you might be able to get away with a crosstab, but chances are you'll need to use formulas to create a manual crosstab. A manual crosstab is where you create a number of formulas at the detail level for each week. Each formula might be something like:

If
//calculate the date range for the 13th week using this or another formula
  {table.date} in DateAdd("ww", -13, {?StartDate}) to (DateAdd("ww", -12, {?StartDate}) - 1)
Then
  1
Else
  0

You'd then create a summary field for each detail column and suppress the detail section afterwards.  This will basically "flatten" out the various vertical records and aggregate them into a single row of data.  It's not the most efficient method, but it works well.
0
 
SarekOfVulcanConnect With a Mentor Commented:
Does DateAdd("ww", -13, {?StartDate}) work for you?
0
 
garyjgsAuthor Commented:
On the money.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.