Greetings.
I am trying to calculate RVU Unit values for charges. Basically, I have two tables:
CHARGES
PostDate
ProcedureCode
Units
RVUPROFILES
ProfileName
ProcedureCode
RVUAmount
EffectiveDate
In the RVUProfiles table, there are multiple profiles for each procedure code, and each may have multiple effective dates with an associated RVU Amount. The user may choose one and only one Profile Name. I want to display, for each charge record, the RVU units for that charge (Units * RVUAmount). I can link by ProcedureCode easy enough -- but picking the right RVUAmount has proved to be tricky. I thought I could do the following to grab the right amount:
FIRSTSORTEDVALUE(RVUAmount, -EffectiveDate)
But, unfortunately, if the PostDate of the charge was 01/03/2008, and there are RVUProfile records for that procedure code with effective dates of 01/03/2008 and 01/01/2009, it pics the RVUAmount for the 01/01/2009 effective date, instead of the appropriate 01/03/2008. I have tried several calculations, including set analysis and if statements... and nothing seems to work. I fear I am making this more complicated than it needs to be, but I'm not seeing a solution.
Please find attached a copy of my QVW. The bottom chart is the one in question (RVU Units). The fields I am trying to get to fill in is the RVU Units and Amount (procedure code 99231 is the troubling one).
Oh - I'm on v8.5 - if that makes a difference.
Thanks!
Sally
