byusteve Member

Don't you need to add a "SELECT DISTINCT" after compiling this table to avoid duplicates wherein the records were on both tables?

From what I've found Domo's BeastMode is broken and limiting the delta to what you're seeing. This is not the indended output of the function as per mySQL documentation. I submitted a ticket quite some time ago citing the issue. I had to calculate instead in a mySQL dataflow. Good luck!

In case anyone is reading this down the road, I ended-up using "TO_SECONDS()" on both dates, calculating the difference, and then converting to hours. It was much cleaner. TIMEDIFF() was behaving differently in BeastMode (was maxing out at 23:59:59) vs. a mySQL dataflow. Additionally by design (for some unknown reason) it…

This code seems to be working, although I have a little more testing to do (i.e. when the times are equal): CASE WHEN `start_datetime` IS NOT NULL AND `end_datetime` IS NOT NULL THEN CASE WHEN DATE_FORMAT(`end_datetime`, '%H:%i:%s') < DATE_FORMAT(`start_datetime`, '%H:%i:%s') THEN ((DATEDIFF(`end_datetime`,…

That will allow me to convert after import, but by then I have already lost the leading zero(s), if present. I'm trying to figure out a way to import w/out loosing any characters. Thanks.

Or have you tried TIMESTAMP(Col2) - TIMESTAMP(Col1)? (Although some conversion may be necessary to get it into "minutes"...)

Have you tried TIMESTAMPDIFF(MINUTE, Col1, Col2)? That works in MySQL, not sure if it will in BeastMode though.

Can you click on your green value above your graph in when editing and where it says "Sum" at the top on the drop-down change it to "Average"?

re: kith_howes solution I use this all of the time within a BeastMode calculation. Works great. Makes Domo not only informative, but immediately actionable.