Creating a Formula to Count Records Based on Date

[kineo1]

Hello all,

I'm still pretty new with Crystal, but I'm learning fast. I'm working on a reporting project and have made several breakthroughs, however at the moment I'm stuck on one formula I've been working on for the last few days. The idea is to count closed incidents (status=2) where they were created before the current date and closed in the current month. However, I'm getting some wacky numbers, and I'm not sure where they are coming from. I'm doing a sum of the formula grouped by rep. I should also mention that because of some limitations in the veiwer, I have had to create a workaround for date ranges. Part of the work-around means I have to change the date of the CurrentDate to the first of the month while running the report.

The formula is:

If ({INCIDENTS.ID_STATUS}=2) and ({INCIDENTS.DT_CREATED}<CurrentDate) and (Month({INCIDENTS.DT_CLOSED})=Month(CurrentDate))

Then

1

Else

0

If anyone has any ideas why this might be giving me the incorrect numbers, they would be most welcome. For example, I am a rep in the system. I should have 11 records that fit the criteria, however it's displaying 132 for me. The correct numbers are, I have 77 total tickets for the month, 66 where created and closed within this month and 11 created prior to this month and created within this month.

Thanks in advance!

 

[synapsevampire]

You're probably experiencing row inflation in the data, place some fields in the details and you'll see that your'[e getting more rows back from the database then you thought, this isn't Crystal's doing, or duplicating rows, it's the way you designed the joins.

What you might do is add in another group by the incident, and then in the Incident group footer do your counting (that way you get one count per incident), as in:

Rep Group Header formula:

whileprintingrecords;
numbervar MyTotal:=0;

Incident Group Footer formula
whileprintingrecords;
Numbervar MyTotal;
If ({INCIDENTS.ID_STATUS}=2) and ({INCIDENTS.DT_CREATED}<CurrentDate) and (Month({INCIDENTS.DT_CLOSED})=Month(CurrentDate)) then
MyTotal:=MyTotal+1

Then in the Rep Group Footer display the variable:

whileprintingrecords;
numbervar MyTotal

Otherwise figure out how to properly retutn the data as one row per incident, often a normalized relational database is designed for INSERTS and so it can't be readily done without advanced queries and database objects.

-k

 

[dgillz]

Apart from SV's advice, there is a good possibility that:

Month({INCIDENTS.DT_CLOSED})=Month(CurrentDate)

is evaluating to true for August 2006, August 2005, August 2004, etc. Is this what you want?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[synapsevampire]

Good point, Don.

So try:

If ({INCIDENTS.ID_STATUS}=2) and ({INCIDENTS.DT_CREATED}<CurrentDate) and (Month({INCIDENTS.DT_CLOSED})=Month(CurrentDate))
and
(year({INCIDENTS.DT_CLOSED})=year(CurrentDate))

-k

 

[kineo1]

Awesome guys! Thank you both! I used the new group Synapsevampire had suggested and was able to go through each incident that was being counted, and sure enough after the 11th one back from the end I could see that it was counting other years as well. I adjusted the formula as you guys both suggested and it is counting correctly now. Now, I can't believe I missed this. Thanks again! That was a tremendous help!