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

Tagged:

Answers

  • GrantSmith
    GrantSmith Indiana 🔴

    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.

  • 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
    GrantSmith Indiana 🔴

    @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
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    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

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