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
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..
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
window , sometimes referred to as OLAP functions:
example:
http://it.toolbox.com/blogs/db2luw/olap-sql-part-3-rank-denserank-rownum-8301