Converting a beastmode text field into a number field
Hello,
I am struggling to get the values my beastmode returns to display in the correct format.
To summarize, I'm looking at blood draw times for donors. The data comes into Domo as phlebotomy start time, and phlebotomy end time (which are both date fields). So, I made the beastmode: TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`). And since phlebotomies are always started and stopped on the same day, this is giving me hh:mm:ss, which is good.
The problem is that this ends up as a text field, and I need it to be a number field. I was able to get the beastmode to be a number field, but now this is the problem I am having:
If the minutes difference between stop and start time are single digit, or if they are multiples of 10 (end in 0), it doesn't display correctly.
For example:
Draw time was 1 hour, 20 minutes (01:20) the beastmode returns 1.2
Draw time was 1 hour, 2 minutes (01:02) the beastmode returns 1.2
Ideally I'd like 1.20 and 1.02
But, if the difference were, say, 1 hour, 23 minutes, it displays correctly as 1.23
This is the beastmode:
ABS(CONCAT((HOUR(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`))),'.',(MINUTE(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`)))))
I tried inserting a time_format function inbetween the minute and timediff portion of the 2nd half of the concat, trying to make minutes display as mm, but I couldn't get it to work
Best Answers
-
Try this:
CONCAT(
HOUR(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`)),
'.',
CASE WHEN MINUTE(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`)) < 10 THEN
CONCAT('0', MINUTE(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`)))
ELSE
MINUTE(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`))
END
)It's checking to see if the minutes are less than 10, if so add a 0 to the minutes otherwise just use the minutes.
This is under the assumption that you're wanting to display this value as hours.minutes and not as fractions of hours. If it's fraction of hours then @MarkSnodgrass's answer should work for you instead.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
You could also just treat it like an actual decimal number and utilize the following BeastMode:
ABS(HOUR(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`)) + (MINUTE(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`)) / 60))
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Answers
-
It's unfortunate that there is not a time-to-serial function that would convert a time to a numeric value like there is for dates in other languages.
I would divide your minute by 60 so that it becomes a properly calculated decimal value. It would look like this:
ABS(CONCAT((HOUR(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`))),(MINUTE(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`))/60
)))I don't have a good dataset to test this with right now, but this should work. I removed the decimal point out of your concat statement since the division of 60 should create a decimal point with the resulting calculation.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Try this:
CONCAT(
HOUR(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`)),
'.',
CASE WHEN MINUTE(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`)) < 10 THEN
CONCAT('0', MINUTE(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`)))
ELSE
MINUTE(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`))
END
)It's checking to see if the minutes are less than 10, if so add a 0 to the minutes otherwise just use the minutes.
This is under the assumption that you're wanting to display this value as hours.minutes and not as fractions of hours. If it's fraction of hours then @MarkSnodgrass's answer should work for you instead.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
You could also just treat it like an actual decimal number and utilize the following BeastMode:
ABS(HOUR(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`)) + (MINUTE(TIMEDIFF(`lte_donation_phleb.stop_time`, `lte_donation_phleb.start_time`)) / 60))
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Thanks for your suggestion, this worked. It is odd to me that, even though this beastmode kept it as a text column instead of a number column, I was still able to put it in my Y axis. When I put my original beastmode (time dif of stop and start time) into my Y axis, I could only get a count. When I changed it to no aggregation, the graph would go blank. I had assumed that this was because it was a text field. Yet your beast mode is showing as a text field but allows me not to aggregate
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 600 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 685 Beast Mode
- 43 App Studio
- 38 Variables
- 655 Automate
- 170 Apps
- 438 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 384 Distribute
- 110 Domo Everywhere
- 268 Scheduled Reports
- 6 Software Integrations
- 111 Manage
- 108 Governance & Security
- 8 Domo University
- 25 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive