Forum Discussion

BhrugeshRathod1's avatar
BhrugeshRathod1
Qrew Member
5 years ago

Finding Min Time and Max Time for a particular date grouped by date and other fields.

Hello everyone, I want to find Min Time and Max Time.
Scenario for report- For each Supervisor there can be many Sales Reps(Like here in pic Supervisor A has Sales Reps X,Y,Z etc. under him). I have a Heures column(Time of Day) in my table. I want to show Start Time(Min Time of Day) and End Time(Max Time of Day) for a particular Sales Rep on that particular day. Like in example I have edited time next to it. I am trying to use formula - Min([Heures],null) and Max([Heures],null) but its showing me Distinct count for them and not actual value. Please let me know the solution how can I show those values.
Also if I try using Table Report it is showing me multiple rows for each time instead of 1 single row with date, start time, end time.
I have a Date(Date/Time) column, Day(Date) column, Heures(Time of Day) column in my table. There can be multiple records for same day for the same person but time may be different like shown in screenshot 2.(Starts with 10:56am and ends with 12:06 pm)





------------------------------
Bhrugesh Rathod
------------------------------
Formulas and functions
Relationships
  • BlakeHarrison's avatar
    BlakeHarrison
    Qrew Captain
    5 years ago
    Unfortunately, MIN and MAX are not options when summarizing Time fields in a Summary Report.  I don't know that there's going to be a way to show what you're looking for in a Summary Report. For the option of a Table Report, you will need a parent record above the level of the individual Time Cards so that you can do a Summary Field for Min Start Time and Max End Time. That table would need to be setup so that each record would be unique to the Sales Rep and Day, so you'd end up with a structure kind of like this:

    Sales Reps < Sales Days
    Sales Reps < Time Cards
    Days < Sales Days
    Sales Days < Time Cards

    You could then do a Summary Field for Min Start and Max End from the Time Cards up to the Sales Days and do your Table Report on the Sales Days table.

    ------------------------------
    Blake Harrison
    bharrison@datablender.io
    DataBlender - Quick Base Solution Provider
    Atlanta GA
    404.800.1702 / http://datablender.io/
    ------------------------------