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

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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!**
  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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!**

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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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!**
  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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!**
  • 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