Link to home
Start Free TrialLog in
Avatar of Carlo
CarloFlag for United States of America

asked on

Crystal Reports Cross Tab Week Comparison Report

I want to be able to create a crystal reports cross tab week comparison from last years order sales to this year order sales for a specific month.
Eventually I want to compare more then 2 years but for now I want just two. The schema should look something like this.

                           MONTH OF SEPTEMBER COMPARISON
            1st week                2nd week      3rdweek                 4th Week
2017     $3,000                  $3,200                $3,000                    $4,000                
2018     $4,000                  $3,500                $3,500                    $4,100
Avatar of Mike McCracken
Mike McCracken

How are you defining the weeks?

Are you looking at calendar weeks? ( Sunday thru Saturday)
Avatar of Carlo

ASKER

The weeks are not being define yet, I think either I have to create a formula for this occurrence or just forget about cross tab and create a dual report within one.
Will you have a date range filter?
Will you use parameters for the date range?

You may be able to use a formula based on the week difference between the date and the first date to create the grouping

Something like

Case DateDiff('w',Minimum({your date field}), {Your date field}) + 1

Use the DIsplay String in the Common Tab of the Format field to change the week number

If CurrentFieldValue >= 4 and CurrentFieldValue <= 20 then
    CStr(CurrentFieldValue,0) + 'th'
Else if CurrentFieldValue MOD 10 = 1 then
    CStr(CurrentFieldValue,0) + 'st'
Else if  CurrentFieldValue MOD 10 = 2 then
    CStr(CurrentFieldValue,0) + 'nd'
Else if  CurrentFieldValue MOD 10 = 3 then
    CStr(CurrentFieldValue,0) + 'rd'
Else
    CStr(CurrentFieldValue,0) + 'th'

Open in new window

It sounds like you're just going to specify a month (September, in your example), and maybe a year, and then look at the weeks in that month (eg. 9/1 - 9/7, 9/8 - 9/14, 9/15 - 9/21 and 9/22 - 9/28).  If so, are you just going to ignore the last 2 or 3 days of the month, or do you want to include them in the 4th week (eg. instead of 9/22 - 9/28, it would be 9/22 - 9/30) ?

 If I'm right, it should be simple enough.  Assuming the simplest case:

 You're going to enter a month and year as parameters.
 You're going to have the record selection formula only include the records for that month and year, and that month in the previous year (so we don't have to worry about excluding other months or years from the crosstab).
 The "date" field in your table is just a date, not a datetime (so we don't have to worry about the time).
 You want to include the last 2 or 3 days of the month in the 4th week.

 You can create a formula like the following (call it whatever you like) and use it for the cross-tab columns:

Local DateVar first_of_month;

first_of_month := {your date field} - Day ({your date field}) + 1;

Select {your date field}
 Case first_of_month to first_of_month + 6 :
  "1st Week"
 Case first_of_month + 7 to first_of_month + 13 :
  "2nd Week"
 Case first_of_month + 14 to first_of_month + 20 :
  "3rd Week"
 Case first_of_month + 21 to first_of_month + 30 :
  "4th Week"
 Default :
  "Nth Week"

Open in new window


 For the 4th week, I just used + 30 as the end date.  That would extend into the next month for some months, like September, but if the record selection formula is only including the month that you select, then there won't be anything in that next month, so the extra day doesn't matter.

 The default "Nth Week" entry at the end is just a precaution.  It should never come up, but, if it does, then you know that something is wrong.

 You can use a simple formula like Year ({your date field}) for the cross-tab rows, or just set the rows to be on your date field and select "for each year" in the Group Options.

 James
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.