Edited by EishiFUN
You need to be logged in to reply to this post and participate in the Geotab Community discussions.
Edited by Genevieve-1051
Hi AGAIN @CBridegroom-1206 !
The reports genius herself @clemu-1737 mentioned that we have a report that already totals times.
The Small Fleet Driver Payroll ELD Report can be found in the Geotab MarketPlace and, "uses Geotab Drive information to accurately track the number of hours a driver clocks in and out."
The only caveat is that this report is recommended for fleets with less than 50 drivers.
Would this solution work better for you?
Best,
Genevieve
Hey, @CBridegroom-1206! :)
There are a couple of different ways that you can do this. Personally, I like to create a new column on the Report tab (name it something like "Week") and then use use the "WEEKNUM" function in Excel on the date column to return a week number all the way down the new column.
NOTE: To create a new column that will show up on the pivot table, you need to insert the column between two existing ones (i.e. not at the end of the data).
From there, you can go over to the the Summary tab with the pivot table and click Data > Refresh All:
This will give you the new dataset to work with in the pivot table to get your totals out by week. And then, presumably, if you run the report on one month at a time, your totals would be the monthly totals overall.
Happy to continue to work on this here so that the whole Community can learn from this stuff, but if you are the same Carol that is one of our Fleet Nav customers with whom I'm working already on a PC report, please feel free to reach out directly via our Support email to request any help you'd like on this (that is, if you would like to work directly with your own data). I'd be more than happy to set aside some time to get you exactly what you're looking for, and we can even go into the basic Excel stuff to get you more comfortable with pivot tables and all the stuff we can do with them if you'd like. 😊
Something else I thought worth mentioning, if you (or anyone else reading this later) would like to become an Excel pro and prefer a more comprehensive self-paced training, I learned a ton of useful stuff via Chris Dutton's Excel courses on Udemy. I took his ones on advanced formulas/functions, data visualization, and data analysis with pivot tables in my spare time over the course of few weeks early last year, and it made me much more comfortable with doing the data stuff in MyGeotab. (NOTE: Those courses go on sale for between $10 and $15 regularly; I'm guessing their next sale will be around St. Patrick's Day. Also, I don't make any money recommending these, they were just really helpful, so I like to share them whenever possible.)
Hope this helps! :)
Best,
Joy
Hi @CBridegroom-1206 ,
Thank you for asking the Geotab community!
For a little clarification, when you say "on duty time" are you referring to hours of service (HOS)? If so, the Duty Status Logs Report can be viewed by daily, weekly, or monthly. To access this report:
You can also find these steps in the article How to Pull and Download HOS Logs Report.
If you are looking to know drivers drive time not in reference to HOS, the Time Card Report can also be viewed by daily, weekly, or monthly and includes total driving and idling time for each vehicle. To access this report:
You can also reference these exact steps in the article How Do I Run a Time Card Report.
Please let me know if either of these reports meets your need or if you need additional assistance.
Warm Regards,
Genevieve
Yeah! Just figured it out! Thanks so much for reaching out! Had to move status to the columns! Duh!
Edited by CBridegroom-1206
Genevieve thanks for reaching out. I can get the data from the HOS logs, filtering D time and on duty time but once its in the pivot table I need help totaling the times, grouping by date, etc etc. 😳 The time card report doesn't correct reflect the on duty time, only the idle time which is close but not exact to the on duty time
Hi again @CBridegroom-1206 !
I was playing around with the Excel pivot table and can not quite figure out how to display the total times or group by date. So I'm going to ask one of our reports geniuses and see if she might know how to make this customization happen for you.
Talk soon,
Genevieve
Edited by Genevieve-1051
Hi AGAIN @CBridegroom-1206 !
The reports genius herself @clemu-1737 mentioned that we have a report that already totals times.
The Small Fleet Driver Payroll ELD Report can be found in the Geotab MarketPlace and, "uses Geotab Drive information to accurately track the number of hours a driver clocks in and out."
The only caveat is that this report is recommended for fleets with less than 50 drivers.
Would this solution work better for you?
Best,
Genevieve
Great I will check into this! 😀
Hey Genevieve! I did check it out and I got something similar to work but this report also does not let me group by date? May I contact Cindy, the reports Genius?
Hi @CBridegroom-1206 !
Let's see if I can get @clemu-1737 to jump in this conversation?! But either way, I'll message her and let her know right now.
Talk soon,
Genevieve
Edited by clemu-1737
Hey @CBridegroom-1206,
To make sure I understand your request fully, Are you needing to total the fleet's total activity for a day/week/month?
Also, you mentioned that you got something similar to work. Are you using your own template?
What issues are you coming across when you try to group by date?
Hi CIndy! Yes I made my own report and it works great on a daily basis but I need it to total every week and month also but I'm not a pro with the pivot table???
Hey, @CBridegroom-1206! :)
There are a couple of different ways that you can do this. Personally, I like to create a new column on the Report tab (name it something like "Week") and then use use the "WEEKNUM" function in Excel on the date column to return a week number all the way down the new column.
NOTE: To create a new column that will show up on the pivot table, you need to insert the column between two existing ones (i.e. not at the end of the data).
From there, you can go over to the the Summary tab with the pivot table and click Data > Refresh All:
This will give you the new dataset to work with in the pivot table to get your totals out by week. And then, presumably, if you run the report on one month at a time, your totals would be the monthly totals overall.
Happy to continue to work on this here so that the whole Community can learn from this stuff, but if you are the same Carol that is one of our Fleet Nav customers with whom I'm working already on a PC report, please feel free to reach out directly via our Support email to request any help you'd like on this (that is, if you would like to work directly with your own data). I'd be more than happy to set aside some time to get you exactly what you're looking for, and we can even go into the basic Excel stuff to get you more comfortable with pivot tables and all the stuff we can do with them if you'd like. 😊
Something else I thought worth mentioning, if you (or anyone else reading this later) would like to become an Excel pro and prefer a more comprehensive self-paced training, I learned a ton of useful stuff via Chris Dutton's Excel courses on Udemy. I took his ones on advanced formulas/functions, data visualization, and data analysis with pivot tables in my spare time over the course of few weeks early last year, and it made me much more comfortable with doing the data stuff in MyGeotab. (NOTE: Those courses go on sale for between $10 and $15 regularly; I'm guessing their next sale will be around St. Patrick's Day. Also, I don't make any money recommending these, they were just really helpful, so I like to share them whenever possible.)
Hope this helps! :)
Best,
Joy
Hi Joy! Yes it's me! Same Carol! Thanks so much for replying! I will be sure to check out that training you suggested. Its kinda busy today but I may reach out to you next week on getting this perfected. Have a great weekend!
C
Cool, nice to see you here! 😁
Do reach out if you have any other tweaks. I've dealt with all those things before--like the times not quite syncing up, etc.--so I'm confident that I can get you whatever you need in no time.
Have a great weekend, too! :)
Best,
Joy