Home » Developer & Programmer » Reports & Discoverer » Count_Distinct in Series
Count_Distinct in Series [message #285805] Wed, 05 December 2007 12:52 Go to next message
jfrano
Messages: 11
Registered: January 2007
Location: New York
Junior Member
I'm doing a Discoverer report, where I'm trying to Count_Distinct(PatientNum) the number of Pateints had a chest Xray with in each Quarter/Year.

What I get is the following
ie
Calc
Count
Dist(PatientID) Quarter Year
195 Q2 2007
212 Q3 2007
278 Q4 2007

Within each Quarter, the Count Distinct works fine. However I only want the Patient ID to be counted in the FIRST Quarter that a Xray occurred. How might I achieve this?

Thanks

[Updated on: Wed, 05 December 2007 13:03]

Report message to a moderator

Re: Count_Distinct in Series [message #286101 is a reply to message #285805] Thu, 06 December 2007 08:20 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
That can be done in two steps:
- determine which is the first quarter per patient
- count that

First quarter: use a analytical function (in calculations), like:
min(quarter) over (partition by patient order by quarter)
(please pay attention to the sort of the quarter, ie should be something like 200701, 200702, etc.)
For details, lookup analytical functions in the sql reference.

Then you get something like:

patient first_quarter
123     200702
124     200702
234     200701
234     200703


And do a count on that:

count(patient) quarter
1              200701
2              200702
1              200703
Re: Count_Distinct in Series [message #286949 is a reply to message #285805] Mon, 10 December 2007 11:58 Go to previous messageGo to next message
jfrano
Messages: 11
Registered: January 2007
Location: New York
Junior Member
Thanks for the reply, one issue is that the Quarter and Year are seperate elements.

[Updated on: Mon, 10 December 2007 12:15]

Report message to a moderator

Re: Count_Distinct in Series [message #286979 is a reply to message #286949] Mon, 10 December 2007 15:00 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Perhaps you can make one item of it, something like this:
to_number(year||quarter)

(if there are any "spare" characters like "quarter 1" or so, that first strip that of, for example using substr)
Re: Count_Distinct in Series [message #287249 is a reply to message #286101] Tue, 11 December 2007 11:05 Go to previous messageGo to next message
jfrano
Messages: 11
Registered: January 2007
Location: New York
Junior Member
skooman wrote on Thu, 06 December 2007 09:20


patient first_quarter
123     200702
124     200702
234     200701
234     200703


And do a count on that:

count(patient) quarter
1              200701
2              200702
1              200703



That would still seem to be counting patinet 234 twice , I would have expected

1 200701
2 200702


Re: Count_Distinct in Series [message #287494 is a reply to message #287249] Wed, 12 December 2007 06:49 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Sorry, patient 234 should have been in the example table (first one) only once. If you have coded the lag/lead stuff correctly, it will.
Previous Topic: Object Liberary of Developer 6 to 6i
Next Topic: Adding an item
Goto Forum:
  


Current Time: Mon Dec 05 12:57:32 CST 2016

Total time taken to generate the page: 0.26637 seconds