calculate difference between two dates

user041053

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 :)

GrantSmith

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 the Date value

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

user041053

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

GrantSmith

@user04105

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)

jaeW_at_Onyx

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:

https://www.youtube.com/watch?v=eifSYZIcPzg

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

https://www.youtube.com/watch?v=cnc6gMKZ9R8&t=4s