filter out multiple episodes

[RepRider]

CRXI with SQL Svr
I have a report that shows a graph of all initial assessments per month.
All consumers have a unique ID but may have several episodes of care. Each episode of care includes an initial assessment. Each episode also has a unique ID.
This first report is just fine for its purpose but now I want to show only consumers that are "new" to us (i.e. only have one single episode of care.

Based on what I have researched here I have tried to use a Group selection formula but the results were exactly the same so I know I wasnt right. This is what I used:

count({Consumer_Service_View.consumer_ID},{Consumer_Service_View.episode_ID})>1

This was accepted but there was no difference in the graphic.

How can I find just those consumers with only ONE episode ID. The ID number makes no difference I just need those with only one episode ID.

Any help is much appreciated!

 

[lbass]

The group should be on consumer ID and the group selection formula should be:

distinctcount({Consumer_Service_View.episode_ID},{Consumer_Service_View.consumer_ID1}) = 1

-LB

 

[RepRider]

Thank you lbass for the superquick reply!

I used that formula and I get an error message that highlights all but the =1 and says there must be a group that matches this field.

The report has groupsings by the start_date, EpidsodeID and ConsumerID.

What am I missing?

 

[lbass]

That means you don't have a group on {Consumer_Service_View.consumer_ID1}. I accidentally appended a 1 at the end--did you copy and paste the exact formula? Should be:

distinctcount({Consumer_Service_View.episode_ID},{Consumer_Service_View.consumer_ID}) = 1

If that's not it, then you probably have a group on the consumer ID from a different table, so change the group selection formula to use the actual group field.

-LB

 

[RepRider]

Yes, I did fix the appended 1. I will check on the table for ConsumerID, that's probably it! Thanks!

Have a good weekend!