Subtracting Dates Based on Other columns
I currently have a data set that looks something like this:
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

Hi @user027926, you'll need a combination of window and date diff functions in order to calculate the difference between the two dates. You will need to ask your Customer Success Manager to "enable window functions in beast modes" in order to make use of this.
A bit of food for thought also, doing something like (CASE when 'Order' = 2 then 'Date Ordered' END)  (CASE when 'Order' = 1 then 'Date Ordered' END) won't work as beast modes are calculated on a row basis. With this beast, either there will be values from Order 1 or 2 but won't have both captured. As a result, you'll have a value and NULL per row and also subtracting null from anything will give null, resulting in a blank output.
9 
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/enus/articles/360044876094NewMagicETLTilesAggregate#3.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.3 
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.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2
Categories
 All Categories
 1.6K Product Ideas
 1.6K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 286 Workbench
 4 Cloud Amplifier
 7 Federated
 2.8K Transform
 91 SQL DataFlows
 585 Datasets
 2.1K Magic ETL
 3.6K Visualize
 2.4K Charting
 649 Beast Mode
 37 App Studio
 33 Variables
 631 Automate
 159 Apps
 432 APIs & Domo Developer
 38 Workflows
 2 DomoAI
 31 Predict
 12 Jupyter Workspaces
 19 R & Python Tiles
 377 Distribute
 107 Domo Everywhere
 266 Scheduled Reports
 4 Software Integrations
 104 Manage
 101 Governance & Security
 6 Domo University
 15 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 93 Community Announcements
 4.8K Archive