Simple DATEDIFF Question
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!!
Best 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.
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!**0
Answers
-
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 ...
0 -
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.
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!**0 -
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!
0 -
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
0 -
... 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"0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive