How to find the last X values by Date

[seballen]

Hello,

I often look up the last test value by date. I do this by creating a Data Item called "LastTest":
maximum ([ResultDate] for [patientID])

and then using a filter:
[ResultDate] = [LastTest]

I'd like to find the last 5 test values for a patient so that I can graph them. Any suggestions?

I was trying to use a running count, but I couldn't seem to order it DESC, so I kept finding the *first* five values in the series.

Thanks,
Sasha

[blom0344]

several solutions come to mind. One would involve calculating the date difference of a large future date and the real date, thus creating a value (difference) you can order by in normal ascending order. Take the first 5 of these based on a running count.

Another solution would be to code it within the model using a window function (assuming the DB supports this)  This works very good for 'resetting' the 'counter' for PatientId.

If you are interested I can give you some code to work with..

[seballen]

Thanks very much for the suggestions. Using a date difference makes great sense and I will give that a shot.

I'm not sure what you mean by using a windows function. If you don't mind, I'd love to hear a bit more about how to go about that method.

Thanks,
Sasha

[blom0344]

window  , sometimes referred to as OLAP functions:

example:

http://it.toolbox.com/blogs/db2luw/olap-sql-part-3-rank-denserank-rownum-8301