• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 888
  • Last Modified:

Unique Users During Date Range (Cognos)

I have a report I'm trying to make in Cognos 8.2 that I'm having trouble with.

I need to count how many patients visit our clinics during a period of time.  I don't need to know home many visits they have had, just the unique patients.

Because I'm filtering during periods of time, I'm getting duplication of patient id's for those patients seen more than once during that period of time.  I need something to behave like a SQL "First", "Last", "MAX" or the like to just give me unique patients.  Right now I can't accurately count the number of patient id's to get a unique list because of this duplication.

Help.
0
IntercareSupport
Asked:
IntercareSupport
  • 3
  • 2
1 Solution
 
RWrigleyCommented:
Does something like:

count([PatientID] for report)

not work?
0
 
IntercareSupportAuthor Commented:
I've been trying a variant of that all morning, but I don't think I have the syntax/concept right.  I was trying  count([PatientID] for [Date]) and things like that, but that left me with the same problem of duplication.

How do I refer to the whole report?  You wrote just "report".  Is that a reserved word, or just your example?  I imagine it's the former, but I'm trying to understand.

Attached is a screen shot of my query screen.
output.PNG
0
 
RWrigleyCommented:
Count([Patient_ID] for report)

should do it.  Set both the aggregation types to "Automatic" or "Calculated".

If that still doesn't work, try:

count(distinct [Patient_ID] for report)

You normally shouldn't need the distinct clause; if that's the only way you can get this working, you'll probably want to revisit the FM model, becuase somethign hasn't been designed correctly.
0
 
IntercareSupportAuthor Commented:
Well, it sort of works.  The aggregation seemed to be the piece I was missing.

Now I've messed up the counts for the final output.  The final total is correct, but it's not breaking down by grouping.  I tried

Count([Patient_ID] for report, [Site])

But that just errors out.  How can I recreate my groupings?
output.PNG
0
 
IntercareSupportAuthor Commented:
Thanks for your help.  I figured out the other part of my subsequent question, but you answered my initial question.  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now