Group by week then sum for three columns

[spcc07]

I am using Crystal XI, Oracle database.
What I am trying to do:
Week Range APP DEC DEP
12/27/2009 - 01/02/2010 12 1 2
01/03/2010 - 01/09/2010 10 2 1
01/10/2010 - 01/16/2010 5 1 1

The numbers under APP, DEC and DEP represent dates that are counted in the week range. This is grouped on Week (copied from previous thread).

@week
datepart("ww",{table.app_date},crSunday, crFirstJan1)

@week_range
totext(maximum({table.app_date},{@week})- dayofweek(maximum({table.app_date},{@week}))+1,"MM/dd/yyyy") + " to " +
totext(maximum({table.app_date},{@week})-dayofweek(maximum({table.app_date},{@week}))+7,"MM/dd/yyyy")

I have been able to use the thread Crystal 8.0 Group by week by year to get one of the columns above to work because I am using the app_date. But, the problem is how to get the other three totals when I am grouping on week and it only has the app_date in it.

Can I create the week and week range formulas without using the date in the table?

 

[Madawc]

It's not clear what you're trying to do. DateAdd or DateDiff would give you a week range much more simply. You can also do your grouping-by-week using a formula field that can get the date from different sources depending on record type, if that helps.

In Crystal XI you should also be able to group by week on the basis of a date and without needing to extract its week number first. Not relevant to your problem but it is easier.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[spcc07]

I need help with the formula to get the week range. I am thinking that I want to set up a formula for the start_date :=
cdate("12/26/2009") then start building the week ranges for the 52 weeks. Then group on the week range.

 

[Madawc]

Do you mean you want each week shown, regardless of whether there is data for it? If you can find a table of dates, or get one set up, you can do a left-outer link from this to your data. That would give zero values.

If you can't add a table, you'll be stuck with creating a 'Mock Crosstab'. Crosstabs are fine if you don't mind the absence of rows or columns with no data, but you said you wanted empty slots to show.

A 'Mock Crosstab' is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then. Crystal should have included an example along with the Crosstabs.

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back. In Crystal 11.5, you can also duplicate formula fields using the Field Explorer.

Each running total will count the record if it was within the criteria - in your case, week/year.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[spcc07]

Thank you.