Monthly Closed Count – Not Open AND Closed 1

[mdelgado100]

I have created a Crystal 8.5 Report that groups by month.
For each month I want -

A count of "incidents/ticket" Opened for that month
- that works fine

A count of "incients/ticket" Closed for that month
- Not fine

-Instead, I'm getting a count of Incidents that where Open AND Closed in the same month. That is, if the incident was Open in the month and also closed in the month it is counted.

-[highlight]What I am wanting is a count of incident that were closed that month regarless of when it was open.[/highlight]
This looks simply...but it hasn't been. If you could help - that would be wounderful...any ideas??

Maria

 

[synapsevampire]

You would have to explain how a closed ticket is identified.

Try posting technical information, such as:

Database/connectivity
Example data
Expected results

If the closed date is a seperate field, use a Running Total against that field, if it's identiifed by a code, then use Running Totals for each count and in the evaluate->use a formula place something like:

{table.code} = "C"

and in the other

{table.code} = "O"

-k

 

[rosemaryl]

Dear Maria,

It occurrs to me why you are having this issue.

You are grouping your report by Incident.Open Date & Time, correct? If so, only tickets that were opened that month are showing for that month's group - hence the opened and closed count not being what you want.

You have two choices:

Do not group the tickets by the Open Date & Time or Closed Date and Time ... create a manual cross tab style report. This is one where you create the formulas to indicate the month.

Lets say you are selecting all Incidents Opened or Closed in the current year.

Now create 12 formulas for the open count:

//Jan
If month({Incident.Open Date & Time) = 1 then 1 else 0

Now, create a formula for each month and align across the report:

Jan Feb Mar Apr ....

Now, do the same for the closed date and time.

Place the formulas in the detail section ... insert grand totals on them and you have the totals for each month, suppress the details section.

Do this for all formulas where you need to calculate something about the Incident for the month.

Choice 2:
Create a subreport that links on a formulas (Start of Month open Date and Time) to (Last Day of Month Open Date & Time) to select tickets that were closed date & time are in that month.

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[mdelgado100]

Thanks, synapsevampire - I am new at this, I try again.

Database/Connectivity
-OracleDataBase / ODBC

Example Data: Among other information each incident has:
Open and/or closed date.
Open and/or closed time.
Open and/or closed state.

Sample formula:

- if {Incident.State:} = 'O' then 0 else 1
- if isnull ({Incident.Close Date & Time}) then 0 else 1
- I have done the reverse of ie, if state is = c then 1..
[highlight]*ALL return incidents Open And Closed in the Month[/highlight]

This one a friend sent to me {@Test_Closed}:
-This will increment (as you suggested with the running totals) it again excludes incidents not open in the month. [highlight]
By the way while the totals are not what I'm looking for, but are same amount consistently. [/highlight]

if {Incident.State:} = 'O'
then 0
else if {Incident.Close Date & Time} in
date(year({Incident.Open Date & Time}),month({Incident.Open Date & Time}),1) to
( if Month({Incident.Open Date & Time}) = 12
then Date(year({Incident.Open Date & Time}),12,31)
else Date(year({Incident.Open Date & Time}),month({Incident.Open Date & Time})+1,1)-1
)then 1 else 0

****From here a do a Sum formula:

Sum ({@Test_Closed},{Incident.Open Date & Time},"monthly")

[highlight] It's hard for me to describe what I'm doing is there a way to share a file? [/highlight]

Thanks aagin...looking forward to your input.

 

[mdelgado100]

Ro,

I was too embarrassed to email you again with my questions. So I thought I'd try this form. I can't keep bothering you.

Thanks again for helping me out.

Maria

 

[synapsevampire]

Hmmm, good call there, Ro, you not even knew the column names, but the types and contents.

That much intimacy demands billing

-k

 

[rosemaryl]

Dear SV,

The question happened to be be on the call center software on which I do the majority of my consulting... I recommend this site to my students ...and Maria was in one of my Crystal classes ...

ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

As I suspected, Ro, just being playful

Hope it's all going as it should.

-k