I have sensor data that takes readings of temperature at different depths every day. I want to have a table that lists the average temperature each month for only readings taken at the *maximum* depth for that month.
Example input data:
| ID | Date | Depth | Temperature |
| 1 | 1/1/2009 | 4 | 50 |
| 1 | 1/1/2009 | 5 | 40 |
| 1 | 1/15/2009 | 5 | 38 |
| 1 | 2/2/2009 | 1 | 60 |
| 1 | 2/2/2009 | 6 | 40 |
Desired output:
| Date | Avg Temp at Deepest |
| Jan-2009 | 39 |
| Feb-2009 | 40 |
I've been trying every set expression I can think of, but it's not giving me the correct results.
I tried Avg({<Depth={"$(=Max(Depth))"}>}Temperature), but that just returned null for each month, *unless* I selected a specific month, and then it would give me the correct value (but just for that month).
I tried Avg({<ID=P({<Depth={"$(=Max(Depth))"}>})>} Temperature), but that gave me values that were just wrong.
I even tried some Avg(Aggr(Avg(... expressions, but couldn't get them to work either.
Any ideas?
