Dates, grouping and evaluating

[Secretgeek]

Good afternoon,

Am using CRXI and am trying to construct a report that identifies how many records were 'open' at a particular historical point in time.

I.e. I have 6500 records each with a separate closed date, I need my report to say x number of cases were open in this particular month. I understand that this would in some form require something like Count (Month([Closed Date])> Month([Group])) followed by a summary field.

What I'm tying myself up with just how to do this. I need Crystal to say 'here is the month I'm checking was this record open in this month?'.

Any help much appreciated.

Thanks in advance.

 

[IanWaterman]

Do you have an open date field?

Simply group report on this field or the closed date field.

In the insert group dialog box when you select a datefield a third box opens with heading

The section will be printed

select each month, you can then do a simple summary count on this group.

Ian

 

[Secretgeek]

Thanks Ian,

I've tried that and that's not giving me what I need. The summary counts only those records in each month. I need a summary count of those records opened in the month with a closed date (or null) after that month.

 

[lbass]

You didn't answer Ian's question--is there an open date field? If you have two date fields, and you have a parameter that returns the first day of the month you are interested in, you could use a formula like this:

if {table.opendate} < dateserial(year({?MonthDate}), month({?MonthDate})+1,1)-1 and
(
isnull({table.closedate}) or
{table.closedate} >= {?MonthDate}
) then 1

This checks to see if the open date is before the end of the month and the close date is sometime after the beginning of the month. Then you could insert a sum on this formula (assuming that you don't have row inflation).

You could alternatively use the date criteria in a running total.

-LB

 

[Secretgeek]

Apologies,

Yes I have an open date field. Currently my only parameter returns the date range that I'm interested in (in this particular case a period of 24 months) but creating one such as you suggest won't be a problem.

Thanks to you both, I'll go and give it a try.