calculate difference between two dates

user041053
user041053 Member
in Magic ETL

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.


image.png


Thanks, in advance :)

Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    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.

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

    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 Coach

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


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

    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

    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"

Categories