Time Differential
I'm trying to calculate how much time on average an employee spends at a location. We have a clock in and clock out time. Currently, I have the forumula set up as:
SEC_TO_TIME(Avg(TIME_TO_SEC(`Time Out`) - TIME_TO_SEC(`Time In`)))
This works fine for almost everything I need. However, if an employee clocks in between 12:00pm and 1:00pm then this formula does not work. It does the calculation as if 12:00pm is 12:00am for some reason and then shows that the employee was there for much longer than they were.
This is real data from this past week (same employees
Employee Name
Time In - 10:00am
Time Out - 13:15pm
Time Spent - 15:15
Time In - 16:30pm
Time Out - 17:00pm
Time Spent - 0:30
I need to know how to correct this formula.
Best Answer
-
Hi,
Your calculation looks correct. I would suggest that your contact Domo Support to further take a look at the issue. If your `Time Out` and `Time In` fields are time data types then this should work. Please go to https://support.domo.com to submit a support ticket. Please provide them with a link to the card, dataset, and/or dataflow in question.
Thanks,
Sperry
1
Answers
-
Hi,
Your calculation looks correct. I would suggest that your contact Domo Support to further take a look at the issue. If your `Time Out` and `Time In` fields are time data types then this should work. Please go to https://support.domo.com to submit a support ticket. Please provide them with a link to the card, dataset, and/or dataflow in question.
Thanks,
Sperry
1 -
Hello user09032,
We were able to get this to work outside of your instance by adding the DATE_FORMAT function to the query. Using your query as an example, that may look like:
SEC_TO_TIME(Avg(TIME_TO_SEC(DATE_FORMAT(`Time Out`,'%Y-%m-%d %T')) - TIME_TO_SEC(DATE_FORMAT(`Time In`,'%Y-%m-%d %T')))
Please let us know if the DATE_FORMAT function gives you better results, otherwise, a support case may be the best way to proceed, as mentioned before.
Darius Rose
**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
I'm using average as the formula is for a summary number. I need it to give me the average time spent on site during the prior week. The way I have it (I think) is it's taking average of Time Out - Time In. The average parentheses is set up to enclose both the time out and time in). I tried using the provided formula (removing avg and with average) and the results are always nil.
0 -
Thank you user09032,
For follow up, we would recommend reaching out to support so they can review your card in more detail. Could you please reach out to them to take those next steps? They can be reached through support.domo.com.
Darius Rose
**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive