I have this above trip data with trip start and end time as date fields. I want to calculate idle time in minutes between trips. Idle time can be defined as the difference between Trip 1’s end time and Trip 2’s start time. For the first and last trip of the day, Idle time can be difference between Shift Start and end time respectively (shift start and end times can be defined as constant for everyday). I tried using PeriodOverPeriodDifference but couldn’t achieve the same since that function cannot be applied over Date fields. Kindly help me calculate the idle time for my dataset.
Hello @praggy03, welcome to the QuickSight community!
To accomplish this, you will need to add 2 calculated fields. First you will want to utilize a lag function to add the start time for the next trip on the same row of your current trip.
It will look like this:
Next Trip Start =
lag(
{Trip Start},
[{Trip End} DESC],
1
)
Now, we can use this field with the dateDiff function to find the difference in minutes from the trip end to the next trip start.
One thing to note, since we are utilizing the lag function, we need to aggregate the value for End Trip. Since we are using this in a table with each trip containing a row, we can use the min aggregation since there will only be one value. This will avoid a mismatched aggregation error. The function will look like this:
Idle Time = dateDiff(min({Trip End}), {Next Trip Start}, 'MI')
I tested this in the Arena analysis so this should give you the expected result. I’ll mark my response as the solution, but let me know if you have any follow-up questions. Thank you!
I tried this out and it worked fine in the table. However I have additional things to mention .
As I mentioned previously, is there a way to define a constant shift time(s) in a day and calculate idle time within the shift time window? A shift time is the period during which the driver(s) are supposed to be engaged on trips and any time outside of that is their free time and should not be accounted in idle time. Is there a way to achieve this?
I want to show the total idle time of the driver(s) selected in the filter as a KPI in my analyses. When I tried the same, I got an error saying "Table attribute references are missing in the field wells. I understood that it’s unable to calculate since I am just adding idle time without any other attributes present. But is there any other way to achieve this ?
I have updated my dataset and added a new column to account for drivers.
Hello @praggy03, do you have a field that determines the shift time for each driver? Or is the shift time standard across drivers? If so, you could utilize an ifelse statement around the dateDiff field to determine if min({Trip End}) >= {shift end time}. If that is true, you can return 0 or NULL, otherwise you can return the dateDiff field above. This will be dependent on how you are storing those shift times but could be an option.
As for your 2nd question, in order to accomplish that, I think the best scenario would be building out that lag function to bring the next trip start time within the dataset. If you can add it in SQL, that would definitely work, but you could also attempt to add the calculated field on the dataset itself, then you may be able to utilize it in the KPI.
Oh, and if Drivers have been added to your dataset, you will need to add the driver as the partition field within your lag function.