Selecting values at timeperiod when sum of all values is highest

3563958

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

Thomas Dodds

you need a level based measure (total across all details/units) by time slot ... then select max.