calculate difference between two dates
Hi,
We have one filed name "Inquiry Date". and we wanted to calculate the difference between the two inquiries.
we need output like mentioned below screenshot (Difference between two dates)
eg:
(01/01/21) - (01/01/21) = 0
(02/01/21) - (01/01/21) = 1
(07/01/21) - (02/01/21) = 5
(15/01/21) - (07/01/21) = 8
Please suggest a solution.
Thanks, in advance :)
Answers
-
Hi @user041053
You'll need to utilize a window function and a date diff function to calculate the difference between the two dates.
DATE_DIFF(`Date`, COALESCE(LAG(`Date`) OVER (ORDER BY `Date`)),`Date`))
DATE_DIFF is calculating the number of days between the two dates
LAG(`Date`) OVER (ORDER BY `Date`) gets the
Date
value from the prior row, ordered in ascending based on theDate
valueCOALESCE is defaulting the date to be either the prior date, or if it's null / the first record it will return the same date to get you a 0 difference.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Hi @GrantSmith :
Thanks for your reply,
l tried with your suggestion but it showing below error message:
"Invalid Formula: This calculation is using a nonexisting function."
0 -
I'm working with too many different SQL version, so sorry! It should be DATEDIFF not DATE_DIFF.
Domo has some issues processing with the coalesce inside and not populating the date correctly. This modified version does the same and sets the difference to 0 if it's the first date in the partition.
COALESCE(DATEDIFF(`dt`, LAG(`dt`) OVER (ORDER BY `dt`)), 0)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
are you doing this in analyzer or ETL? if you're doing it in Analyzer i don't expect this function to work once you try to apply aggregation. Typically window functions (the LAG() function) require two aggregations, once to occur BEFORE the window is applied at the granularity of the aggregated data, and then the window applies AFTER the GROUP BY clause in Analyzer...
it's a long video but here's the explanation:
A BETTER SOLUTION
for the type of fact table you're trying to create ( a fact where you calculate the difference from the previous activity) oftentimes it makes sense to hardcode the previous date into your data using a Rank & Window function with LAG.
That said, the downside of hardcoding your LAG() column is that it will no longer respond to filters.
Here's another in depth walk through of using LAG in analyzer
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