First call of the day.

[BengtS]

Hi everyone.

This is my first day here, so I tried hard to find an existing thread about my question, but no luck.

My company have sales representatives working from home, so we at HQ want to see their first phone call of the day, (and maybe the last) at a later time.

I managed to get the first call of the day of ANY seller, but not for each one individually.
As you can see from the picture. All the names has the same time stamp for first call.
How do I get everyones First call.
I used this question to get the first call.

index(${phone_call_extended.date_time_of_day},min(if(NOT is_null(${phone_call_extended.date_time_of_day}), row(), null)))

I have been stucked at this issue for a week now.

Can anyone help. Dont hav SQL in my version of looker.

[Dawid]

Welcome to the Community @BengtS ! This is one of the cases that are very often request in here - ability to use MIN and MAX on dates as a measure. Unfortunately Looker doesn’t allow us to do that via functions or Table Calculations.

If you do have access to LookML, I would create a measure called First Call, with the following code

measure: first_call {
    sql: MIN(${phone_call_extended.date_time})
}

But you’d have to make sure that you are referencing datetime field not just time, as this might not work (not sure 100%)

And you can find more information about the above here in Looker Docs: https://help.looker.com/hc/en-us/articles/360023425454-Max-or-Min-Date-Measure

Believe me, the day we have min/max for dates in functions, I will be very happy!