The "Bucketing" series of articles are oldies but goodies.... I'm seeing this a lot in the field again as folks are cranking up serious sales reporting as the first quarter nears to a close.
When grouping (or "bucketing") data by a date field, it is important to make sure that your groups are mutually exclusive.
Let us suppose that we are breaking down rows from a table called
INVOICES based upon INVOICES.DATE. We just want to see which invoices
are "out" thirty days, and which are "out" sixty days.
So, if our "30 Days" group is calculated thus:
INVOICES.DATE > CURRENTDATE AND INVOICES.DATE <= CURRENTDATE - 30
(give me invoices that are between zero and thirty days old)
Our "60 Days" group must necessarily exclude day 30 and begin on day 31:
INVOICES.DATE >= CURRENTDATE - 31 AND INVOICES.DATE <= CURRENTDATE - 60
potential danger here is making our second bucket start on day 30, a
common mistake. This would mean that any invoice with a date landing on
day 30 would belong to both buckets, therefore skewing our results.
This cannot be overstated. Crystal known nothing about your date buckets, just the code that you provide it.
better solution could be to use the "automagical" Crystal functions
AGED0TO30DAYS or AGED31TO60DAYS, both of which are available under the
"Date Ranges" function tree in the formula editor.
prefer the "manual" technique as described above, as it allows you to
specify exactly what ranges are being used. (i.e. some clients like to
use 40/80/120 instead of 30/60/90)