I am having trouble selecting the maximum occupancy values in a specific time chunk, when I have multiple units. My data is stored in 15 minute intervals, and I want OBIEE to return the values for each hour when the sum of the patients in all three units is highest. When looking at the table below, the highest total is 102 @ 1:15. so I want OBIEE to return the vales of each unit at 1:15. (A=100, B=2, C=0)
When using the code below, and it returns the highest patient occupancy each unit had individually during the hour.(A=100, B=50, C=30)
MAX("Snapshot"."Patients" by "Snapshot Date"."Date", "Snapshot Date"."Hours", "Area"."Unit Names")
How do I get OBIEE to return all the values at 1:15? (A=100, B=2, C=0)
| Patients/Time | 1:00 | 1:15 | 1:30 | 1:45 |
|---|
| Unit A | 0 | 100 | 35 | 20 |
| Unit B | 50 | 2 | 50 | 15 |
| Unit C | 10 | 0 | 0 | 30 |
| TOTAL | 60 | 102 | 85 | 65 |
