Activation Time <10

Hiraayub
Hiraayub Member
in Charting

I'm trying to get a percentage of less than 10 mints, in dataset I have values where start time is before the arrival time and instead of counting them as negative it displaying positive, how Can I fix this.

SUM((CASE 

  WHEN (TIME_TO_SEC(timeDIFF(`Start Time (UTC)`, `Arrival Time (UTC)`)) / 60) <=10 THEN 1

  END))

  /

  SUM((CASE WHEN `Case Number` is not null then 1

    end))

Activation Time.PNG


Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Ah the reason I'm not a fan of TIMEDIFF. In cases like this I prefer to use UNIX_TIMESTAMP and math to calculate the difference in seconds between two different timestamps.


    SUM((CASE

  WHEN ((UNIX_TIMESTAMP(`Start Time (UTC)`) - UNIX_TIMESTAMP(`Arrival Time (UTC)`)) / 60) <=10 THEN 1

  END))

  /

  SUM((CASE WHEN `Case Number` is not null then 1

  end))


    One question of clarification - If a start time comes 15 minutes before the arrival time (-15 minutes) should that be included since -15 < 10 or should it be excluded since the absolute duration is greater than 10? If it should be excluded you can wrap the subtraction in the ABS function to get the absolute value.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    You can use the ABS() function to turn any negative number into a positive number.

    ABS

    Returns the absolute value for all values in a numeric column.In other words, any negative values become positive, and positive values stay the same. This is valuable when you want to see aggregation without considering positive and negative values.

    ABS('Operating Budget')

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

Answers

  • GrantSmith
    GrantSmith Coach

    You can utilize an additional clause inside your case statement to swap the values if necessary:

    SUM((CASE

  WHEN `Start Time (UTC)` <=  `Arrival Time (UTC)`  AND (TIME_TO_SEC(timeDIFF(`Start Time (UTC)`, `Arrival Time (UTC)`)) / 60) <=10 THEN 1
  WHEN `Start Time (UTC)` > `Arrival Time (UTC)` AND (TIME_TO_SEC(timeDIFF(`Arrival Time (UTC)`, `Start Time (UTC)`)) / 60) <=10 THEN 1

  END))

  /

  SUM((CASE WHEN `Case Number` is not null then 1

  end))
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Hiraayub
    Hiraayub Member

    Thanks, but this is giving me no results also I need to include those rows.

  • MarkSnodgrass
    MarkSnodgrass Coach

    Try adding ELSE 0 in your case statement like this:

    SUM((CASE 
  WHEN (TIME_TO_SEC(timeDIFF(`Start Time (UTC)`, `Arrival Time (UTC)`)) / 60) <=10 THEN 1 ELSE 0
  END))
  /
  SUM((CASE WHEN `Case Number` is not null then 1 ELSE 0
    end))


    **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.
  • Hiraayub
    Hiraayub Member

    It will still not solve the problem as the Response time should be negative and not positive. like the first one -8 mints

    Activation Time.PNG


  • MarkSnodgrass
    MarkSnodgrass Coach

    Interestingly, when I put this into my beast mode:

    TIMEDIFF('4:46:11 PM','4:54:28 PM')

    It resulted in a value of 23:51:43 rather than the negative approx. 8 minutes as you would expect. It seems as though Domo's implementation doesn't match MySQL's implementation, which is what it typically follows.

    I think if you took this knowledge and added your time to sec function and then subtracted from 86400 (seconds in a day) and divide by 60, you will get to your minutes and be able to put this into your case statement.

    I would suggest building this up gradually after adding each function and view the results in the table card to make sure you are seeing the results you would expect rather than trying to jump to the final result.

    Hope this helps.

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

    Ah the reason I'm not a fan of TIMEDIFF. In cases like this I prefer to use UNIX_TIMESTAMP and math to calculate the difference in seconds between two different timestamps.


    SUM((CASE

  WHEN ((UNIX_TIMESTAMP(`Start Time (UTC)`) - UNIX_TIMESTAMP(`Arrival Time (UTC)`)) / 60) <=10 THEN 1

  END))

  /

  SUM((CASE WHEN `Case Number` is not null then 1

  end))


    One question of clarification - If a start time comes 15 minutes before the arrival time (-15 minutes) should that be included since -15 < 10 or should it be excluded since the absolute duration is greater than 10? If it should be excluded you can wrap the subtraction in the ABS function to get the absolute value.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Hiraayub
    Hiraayub Member

    Thank you Grant, this has solved the problem but not sure why it's counting the time more than 10 mints :(

    it's only few rows and doesn't effect the # that much but still...


    Activation Time.PNG


  • Hiraayub
    Hiraayub Member

    is there a way to make the negative numbers into positive?

    As this solve the problem with time but then when you sum it's not the right number.

    Activation Time.PNG


  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    You can use the ABS() function to turn any negative number into a positive number.

    ABS

    Returns the absolute value for all values in a numeric column.In other words, any negative values become positive, and positive values stay the same. This is valuable when you want to see aggregation without considering positive and negative values.

    ABS('Operating Budget')

    **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.
  • Hiraayub
    Hiraayub Member

    thank you, Grant & Mark.

    This really solve the problem :)

Categories