Max interval on specific dates

[Anonymous]

Hi,

I have some issues when getting a value based on the min en max interval between two times on a specific date.

Date             Name     Scheduled_Time     Actual_Time    

11-11-2017     Paul     11:35                    11:32

11-11-2017     David     11:20                  11:59

12-11-2017     Frank       09:20               09:11

13-11-2017     Eva          09:30              09:22

13-11-2017     Susan      09:15               09:00

13-11-2017     Daniel     10:00                    09:59

13-11-2017     Andy     11:20                    12:00

What I want is to show the name of the person with the highest negative difference between the Actual_Time and Scheduled_Time based on today, where in this example today is 13-11. I actually use an Interval to look between scheduled and actual time. So I suppose I have to use Min or FirstSortedValue or something?

By the way, I want the same for the positive difference between both, so if you want you can help me also with such an example. If I have one of both, I will be able to solve the other one by myself.

Thank you in advance.

[ieuyar]

try below expressions:

for the case 1:

if(Actual_Time<Scheduled_Time and Date=date(Today(),'DD-MM-YYYY'),

aggr(max(fabs( Actual_Time-Scheduled_Time)),Date,Name))

for the case 2:

if(Actual_Time>=Scheduled_Time and Date=date(Today(),'DD-MM-YYYY'),

aggr(max(fabs( Actual_Time-Scheduled_Time)),Date,Name))