datediff at row levels
Hello,
I'm doing analysis of our repetitive customers. I want to create a calculation that will show the days difference between order 1 and order 2 based on the company. Each order has its own row. I created rank column, that assigns 1 to first order, 2 to the second etc… How can I create a calculation that will subtract date of order 2 for company x from order 1 for company x?
Thank you
Best Answers
-
@zuchu
You should be able to do this using the Pivot tile in Magic ETL.
You can find more information in our Knowledge Base article:
Basically, you can pivot the rows based on their order number, which will create new columns for each order. You can then use the DATEDIFF formula withOrder 1
,Order 2
.
Here are a few screenshots that walks through those steps with very simple dummy data:
Starting Data:Pivot Tile:
Output Data:
Hope this answers your question!2 -
I think that this would need to be done in the same ETL that you are using to add the order rank. Once you have each companies orders ranked in ascending order, then you would want to use a rank/window function to create a "Prior Order Date" field. This would be done by using a LAG function offset by 1 row and partitioned by Company. You can then use DATEDIFF(`Order Date`,`Prior Order Date`)
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0
Answers
-
@zuchu
You should be able to do this using the Pivot tile in Magic ETL.
You can find more information in our Knowledge Base article:
Basically, you can pivot the rows based on their order number, which will create new columns for each order. You can then use the DATEDIFF formula withOrder 1
,Order 2
.
Here are a few screenshots that walks through those steps with very simple dummy data:
Starting Data:Pivot Tile:
Output Data:
Hope this answers your question!2 -
I think that this would need to be done in the same ETL that you are using to add the order rank. Once you have each companies orders ranked in ascending order, then you would want to use a rank/window function to create a "Prior Order Date" field. This would be done by using a LAG function offset by 1 row and partitioned by Company. You can then use DATEDIFF(`Order Date`,`Prior Order Date`)
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive