Count of client with only one episode

[RepRider]

CRXI and SQL
I am using the Hamady Case Flow report that shows for a given time period the number of open cases at the start and at the end of the report time period. It also shows cases opened and closed during the report time period. There are also parameters for the user to define the reporting time period.

I would like to add a count of the cases that have never been here before that were opened during the time period or, that is, all brand new cases that were opened. All of the data pieces above are gathered through 4 different forumulas.

In our system each client will have a unique ID and can be opened and closed a number times. Each opening will have a unique "Episode_ID." My count could come from the fact that those opened consumers have only one unique episode_ID. I am struggling on how to write a formula that will do that. Can anyone please point me in the right direction?

The formula for cases opened during the time period is:
If {Episode.BeginDate} in {?StartDateRange} to {?EndDateRange} then 1 else 0.

Also, an Episode_ID could be before the start of the report time period. I don't know where to start with this. Any help is appreciated!! Thoughts?

 

[lbass]

If you group by client ID, and if all episodes are allowed into the report (so that you can really test for what is new), then you can go to report->selection formula->GROUP and enter:

distinctcount({table.episodeID},{table.clientID}) = 1 and
{Episode.BeginDate} in {?StartDateRange} to {?EndDateRange}

-LB

 

[lbass]

Oops. Then to get the count of these customers now displayed, you can use a running total that does a distinctcount of clientID, evaluate on change of group: clientID, reset never. Place the running total in the report footer.

-LB