Grouping by Month/Week

[TheMagikWand]

This may seem like the easiest question around, but i have no way of solving it myself to here it goes:

I have a report that i wish to sort by week, but in my database, i only have specific days for specific events (exe. 50 orders for sept 2, '03 and 25 for sept 5, '03)
my date is listed in the database as a number field, not a string, so how do i manipulate it so that i can sort by week or month rather than just by date?

thx in advance
B U D

 

[HowardHammerman]

Hi Bud,

first you will need to convert your number-format date into a date format. The way to do this depends on the way the info is entered. Do you have the number of days since the beginning of the year, or do you have a YYYYMMDD format, just as a number. There are several ways to make the conversion.

The next question is how do you want to handle the dates that are not there? If you just want to group the dates that you do have sales for, then there is no problem. Otherwise you may have to add a table.

Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[TheMagikWand]

hey howard,

to answer the first Q i have mine listed in CCYYMMDD format, and have converted them to a date using the numbertodate(x) dll i d/led from the seagate website.

secondly each entry has its own specific date, so missing dates will not be a problem

 

[HowardHammerman]

OK. Good work. Now what about the days that you have no transactions? Is that a problem or not? That is, do you want to show that on Monday, Sept 9 there was zero sales or do you only want to show those dates that there were sales?

Assuming the latter, your next step is to group on your newly created date formula first by month, second by week. let Crystal do the work.

Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269