Converting a beastmode text field into a number field

Jbrorby

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

 

 

 

 

GrantSmith

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.

GrantSmith

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))

 

 

MarkSnodgrass

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.

 

GrantSmith

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.

GrantSmith

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))

 

 

Jbrorby

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