Subtracting Dates Based on Other columns

user027926
user027926 ⚪️
in Apps

I currently have a data set that looks something like this:

image.png


I want do compute a Date Diff for orders from the same account between orders 1 and 2.

So-- find accounts that have the same number in column A

Determine which two orders (rows) to pick, based on column B

Subtract the corresponding dates in column C


Example: Find Account A123-- subtract it's first order date from its second: 03/04/2021- 01/01/2021 and tell me how many days have passed.


How can I do this? Even better-- how can I do this in an ETL or BeastMode?


Thanks!

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    I find it is easiest to do this in Magic ETL utilizing the Rank & Window tile. Choose the Lag function and then choose order date asc and partition by your account number and order number. This will put the previous order date (when there is one) next to the next order. You can then use the formula tile and the datediff function to find the difference between the two dates. Here's a link to the KB article for Rank & Window.

    https://domohelp.domo.com/hc/en-us/articles/360044876094-New-Magic-ETL-Tiles-Aggregate#3.

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Indiana 🥷

    Here's a Beast Mode that is doing what @amehdad is describing:

    DATEDIFF(LEAD(MIN(`Date Ordered`)) OVER (PARTITION BY `Account Number` ORDER BY `Which Order Number is this`), `Date Ordered`)

    This will calculate the difference between each order based on each customer.

    Screen Shot 2021-11-20 at 7.55.31 PM.png




    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

Categories