Tuesday, December 29, 2009

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)

14 comments:

Sadia said...

A effective paper composing task obliges that weblink the learner does exceptional research to get prepared with the materials needed.

help me write my essay said...

Our writers work with any subjects, timing and complexity levels. I just wanna encourage you to read review

Juan Hose said...

All our specialists in writing essays have higher education. They tackle a great quantity scientific fields which makes it possible for our students to submit an order for any argument required. Besides, you will be with the abundance of tasks the adepts complete.

Jeanne Foster said...

proofreading and editing services will provide you with 100% plagiarism free paper.

Morris Tombs said...

HI!
Thanks for so simple to understanding information ) Check this service teachingcareers.net
Good luck!

Salena said...

In opposition to all those investigations I have already found on the subject, the one is replete with advanced concepts. This site regularly puts a great number of captivating investigations on the vital items.
toptermpapers.org

Brazil Honeymoon Resort said...

Using a stocks tip list like this is a smart move to make if you are inexperienced or don’t have the time to put towards analytics yourself like hardly any of us do.Brazil Honeymoon Resort

texas web design said...

I was being so curious why this all posts talk about compliment with this one. So I just read of what the content of this and began to think that there is no really doubt why this post talks about complimenting and uses praising word about this. So thanks for sharing this great post.texas web design

Opilsa said...

Great writing, check also free betting tips

Paulo said...

It might be conceivable to discover private credit online without a cosigner, yet you will probably have pay higher loan costs. The administration advances are the most ideal approach to get a private credit without a cosigner. www.usacheckcashingstore.com/san-diego

Thomas pedro said...

Advances are dispensed to either understudies or their gatekeepers by the government, banks, private moneylenders or the school itself. Most credits have 10-year reimbursement periods and their rates of intrigue change on the first of July consistently. cash advance

Thomas pedro said...

People who are have shockingly been behind their installments or are battling with their present credit can even now fit the bill for the other program known as Homes Affordable Modification. cash advance

marko said...

However with the achievement rates of such applications getting passed being entirely low, it is great to see how you can meet all requirements for a home advance alteration. This will help you to spare heaps of time, exertion and cash spent generally on getting altered an advance that is unmistakably a lost case. Cash Advance Chula-vista

marko said...

You can apply for instruction advance from the private banks too yet you ought to have a decent credit record for that. These private banks have higher financing cost and they keep an extremely solid historical verification. Check Cashing