Monday, July 25, 2011

Crystal Reports : The Perils of Double Bucketing

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

The 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.

A 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.

I actually 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)

No comments: