Skip to content

    • Magic ETL

    Magic ETL

    Simple DATEDIFF Question

    Member
    in Magic ETL

    As I understand it DATEDIFF will return (in number of days) the difference between two sets of dates. My dataset is pretty rudimentary so it seemed pretty cut and dry but when I do DATEDIFF (for days) it returns an incorrect figure.

     

    This is the beast mode I am using:

     

    DATEDIFF(`Form Complete Date`, `Form Sent Date`)

     

    I attached a screenshot showing the Form Sent Date (10/14/2020) and Form Complete Date (11/13/2020). Not sure why it's returning 5 Days...the aggregation is COUNT since we're counting up the number of days between sent/completion. I tried sum just to see and its returning 150 days...

     

    thanks!!

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • Coach
      Answer ✓

      Hi @user32470 

       

      I've seen the DATEDIFF function not always work as intended some times so I utilize the UNIX_TIMESTAMP function to convert it to the number of seconds since 1970-01-01 and then do some simple math.

       

      1. ROUND(((UNIX_TIMESTAMP(`Form Complete Date`) - UNIX_TIMESTAMP(`Form Sent Date`)) / 60 / 60 / 24) - 0.5, 0)

       

      Subtracting 0.5 and using ROUND is the same as truncating a number which gives you the number of whole days in-between the two dates.  60/60/24 converts the difference in seconds to the difference in days.

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

    Answers

    • Member

      The pivot card is set to "ALL TIME" ...It's just showing 10/12/20 because the date range field defaulted to a 3rd date column. It shouldn't affect the beast mode, but I changed the date range field just to double check and its still returning 5 days ...

    • Coach
      Answer ✓

      Hi @user32470 

       

      I've seen the DATEDIFF function not always work as intended some times so I utilize the UNIX_TIMESTAMP function to convert it to the number of seconds since 1970-01-01 and then do some simple math.

       

      1. ROUND(((UNIX_TIMESTAMP(`Form Complete Date`) - UNIX_TIMESTAMP(`Form Sent Date`)) / 60 / 60 / 24) - 0.5, 0)

       

      Subtracting 0.5 and using ROUND is the same as truncating a number which gives you the number of whole days in-between the two dates.  60/60/24 converts the difference in seconds to the difference in days.

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

      Thanks, Grant! You've been more than helpful with me the past 2 days!! I like the inclusion of the Unix Timestamp - learned something new.

       

      I found out why it wasn't returning the correct number of days from my first attempt - I was using a pivot table and wasn't aware the function returns the number of days "as-is" without needing aggregation. I switched it over to the "megatable" which allows no aggregation and voila we have the right count.

       

      thanks again!

    • Member

      It did not work for me:

      ROUND(((UNIX_TIMESTAMP(`UnderwritingClearToCloseDate`) - UNIX_TIMESTAMP(`UnderwritingSubmittedDate')) / 60 / 60 / 24) - 0.5, 0)  I can clearly see the 2 fields with dates

    • Coach

      ... what didn't work?  where did it break?

      i guarantee you the ROUND() function works and UNIX_TIMESTAMP() works.

       

      so why doesn't it work with your beast mode?  peel away the layers.  remove ROUND() function.  does it work?  Remove the math.  you probably have a parenthesis in the wrong place.

      Jae Wilson
      Check out my 🎥 Domo Training YouTube Channel 👨‍💻

      **Say "Thanks" by clicking the ❤️ in the post that helped you.
      **Please mark the post that solves your problem by clicking on "Accept as Solution"

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories