OraFAQ Forum: Reports & Discoverer » Pick the Latest Record (Rank?) and Count

Home » Developer & Programmer » Reports & Discoverer » Pick the Latest Record (Rank?) and Count (Oracle BI Discoverer )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Pick the Latest Record (Rank?) and Count [message #473944]

Wed, 01 September 2010 10:50

amalison
Messages: 2
Registered: September 2010
Location: Canada

Junior Member

Hi,

Am working on a workbook to count the number of enrolments and withdrawals in the program. My data looks like this
name semester status year
A 1 enrol 2010
A 2 withdraw 2010
A 3 enrol 2010
B 1 enrol 2010
B 2 withdraw 2010

I want to count their latest status only. It should come up with
Total Enrol - 2
Total Withdrawn - 1

For total Withdrawn, I tried 'rank' and filter to equals 1 but it does not allow me. Is there any way to have this work?
Here's my calculation:
count(decode((FIRST_VALUE(status) OVER(PARTITION BY year, name ORDER BY semester DESC)),'withdraw', name))
It tells me that 'Aggregation of Analytic function not allowed'

Report message to a moderator

Re: Pick the Latest Record (Rank?) and Count [message #473963 is a reply to message #473944]

Wed, 01 September 2010 11:12

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

Well it'd help if you gave the full query.
You should be able to make it work with some nesting:

SELECT count(decode(col1,'withdraw', name))
FROM (SELECT FIRST_VALUE(status) OVER(PARTITION BY year, name ORDER BY semester DESC))
      FROM .....
      WHERE .....
     )
GROUP BY .....

Also can you have a read of the orafaq forum guide and follow it in future.

Report message to a moderator