Wednesday, February 27, 2013

Crystal Reports : Grouping or "Bucketing" Data

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

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)

1 comment:

Biddut Debnath said...

Hello sir, How are you today?

Here has so alternative posting about of article Crystal Reports : Grouping or "Bucketing" Data.It is a beauty full site for us. This site is a help us many many way .I also like it.This is a nice post. So i want to share Click-n-earn Get Compensated Get paid to click and view websites, Get Compensated To Improve, Buy Cost-effective Banner Promotion PTC Promotion Email advertising, Affordable ad Promotion, Affordable PTC Promotion, Produce Cash On the internet, ways to generate money online, Get Compensated For Website Hits work from home USA And More. Try It Out Today!

Thank You Very Much For a Nice & Cool Article.