Calculating 13 weeks prior to a date.

Posted on 2011-10-27
Last Modified: 2012-05-12
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?
Question by:garyjgs
    LVL 1

    Assisted Solution

    Does DateAdd("ww", -13, {?StartDate}) work for you?
    LVL 26

    Accepted Solution

    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 :

    {} 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:

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

    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.

    Author Closing Comment

    On the money.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now