window function
I have the following table and i would like to understand how to get the difference between dates:
id | status | date
1 | disconnect | april 4
1 | reconnect | april 7
2 | disconnect | june 4
I would like to understand the average days that persons reconnect in the entire dataset. Can a partition function work here? The result here should see customer 1 it takes 3 days to reconnect.
Any help would be appreciated. Thanks!
Best Answers
-
Yes, a Lag function of 1 (Window and Rank Tile), partitioned by customer and sorted by date ASC, should be able to add next to each row the date of the previous entry, so your example would become:
id | status | date | previous date
1 | disconnect | april 4 |
1 | reconnect | april 7 | april 4
2 | disconnect | june 4 |
From here, you can calculate the time from the previous date to the date and that will tell you the number of days between changes. Do a card that AVGs this time by the customer (id), filtering only to Status of Reconnect and you should get what you're looking for. (You can change the aggregation for different answers)
0 -
Is it possible to have two disconnects in a row for an ID or does it always alternate disconnect/reconnect? If it always alternates, you could rank by id/status, split the dataset into disconnects and reconnects, and then join on id and rank. So first disconnect joins with first reconnect.
If the pattern breaks, that doesn't work, though.
This feels a bit hacked together, but I played with this solution: use filters to split your dataset into Disconnects and Reconnects, do a full join to multiple every disconnect by ever reconnect. Then, filter for just reconnects with dates greater than disconnects (or no reconnnects). Then rank disconnects by reconnect date and filter for just the 1st reconnect after the disconnect. Something like this:
The formula tile:
Kind of inefficient since it multiplies the dataset, so I'd be curious if someone has something more elegant for that case.
Please π‘/π/π/π this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0
Answers
-
Yes, a Lag function of 1 (Window and Rank Tile), partitioned by customer and sorted by date ASC, should be able to add next to each row the date of the previous entry, so your example would become:
id | status | date | previous date
1 | disconnect | april 4 |
1 | reconnect | april 7 | april 4
2 | disconnect | june 4 |
From here, you can calculate the time from the previous date to the date and that will tell you the number of days between changes. Do a card that AVGs this time by the customer (id), filtering only to Status of Reconnect and you should get what you're looking for. (You can change the aggregation for different answers)
0 -
thank you for this, very helpful. So, what if the data looked like this? If I did this with the partition, it would pick up the last disconnect date? I would only want to see the difference between the dates between disconnect and reconnect.
id | status | date
1 | disconnect | april 4
1 | reconnect | april 7
2 | disconnect | june 4
1 | disconnect | may 5th
0 -
Is it possible to have two disconnects in a row for an ID or does it always alternate disconnect/reconnect? If it always alternates, you could rank by id/status, split the dataset into disconnects and reconnects, and then join on id and rank. So first disconnect joins with first reconnect.
If the pattern breaks, that doesn't work, though.
This feels a bit hacked together, but I played with this solution: use filters to split your dataset into Disconnects and Reconnects, do a full join to multiple every disconnect by ever reconnect. Then, filter for just reconnects with dates greater than disconnects (or no reconnnects). Then rank disconnects by reconnect date and filter for just the 1st reconnect after the disconnect. Something like this:
The formula tile:
Kind of inefficient since it multiplies the dataset, so I'd be curious if someone has something more elegant for that case.
Please π‘/π/π/π this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive