Hello,
I need to display the most recent value available, along with the date it is associated with, even when other values are associated with an even more recent date. (Then, second step: compare that value with another value associated with that date, to determine what color to display the first value in.) Attached is a sample qvw. Also below is a smaller set of sample data.
I am able to successfully do what is needed for the first two columns, using
=FirstSortedValue([OP MARGIN BCH],-DateKey)
since there are no later dates than the dates for the values I want.
But I want a chart to display for
Monthly Mortality, like this:
8/1/2022 0.1 (in red) 0.85
and for New Patient Acquisition, like this:
9/1/2022 82988 (in red) 85000
sample data (also in attached qvw)
| DateKey |
OP MARGIN BCH |
OP MARGIN S&P |
MONTHLY MORTALITY RATE BCH |
MORTALITY RATE - TARGET |
NEW PATIENT ACQUISITION |
NEW PATIENT ACQUISITION - TARGET |
| 7/30/2022 |
- |
- |
- |
- |
- |
- |
| 7/31/2022 |
- |
- |
- |
- |
- |
- |
| 8/1/2022 |
- |
- |
0.1 |
0.85 |
82,809 |
84000 |
| 8/2/2022 |
- |
- |
- |
- |
- |
- |
| 8/29/2022 |
- |
- |
- |
- |
- |
- |
| 8/30/2022 |
- |
- |
- |
- |
- |
- |
| 8/31/2022 |
- |
- |
- |
- |
- |
- |
| 9/1/2022 |
- |
- |
- |
- |
82988 |
85000 |
| 9/2/2022 |
- |
- |
- |
- |
- |
- |
| 9/30/2022 |
- |
- |
- |
- |
- |
- |
| 10/1/2022 |
- |
- |
- |
- |
- |
86000 |
| 10/2/2022 |
- |
- |
- |
- |
- |
- |
| 10/3/2022 |
- |
- |
- |
- |
- |
- |
| 10/4/2022 |
- |
- |
- |
- |
- |
- |
| 10/31/2022 |
- |
- |
- |
- |
- |
- |
| 11/1/2022 |
- |
- |
- |
- |
- |
87000 |
| 11/2/2022 |
- |
- |
- |
- |
- |
- |
| 11/27/2022 |
- |
- |
- |
- |
- |
- |
| 11/28/2022 |
- |
- |
- |
- |
- |
- |
| 11/29/2022 |
- |
- |
- |
- |
- |
- |
| 11/30/2022 |
- |
- |
- |
- |
- |
- |
| 12/1/2022 |
0.2 |
0.019 |
- |
- |
- |
88000 |
