Calculate time between incidents
I am a newbie, thanks for helping
I have data in a table that contains a timestamp of incident creation time on each row.
I am trying to show the average time between occurrences.
Basically, I want to write a calculation that with give me the average time that have passed since the previous occurrence (row)
I know this is wrong, but Something like...?
(Row 2 'sys_create_time') - (Row 1 'sys_create_time')
(Row 3 'sys_create_time') - (Row 2 'sys_create_time')
average of all
You can use DATE_DIFF or some math with UNIX_TIMESTAMP (returns the number of seconds as an integer since 1970-01-01 which I prefer as I've had some odd results with date diff.)
It appears you've got a single data column
Assuming you're doing this in a beast mode it'd look something like this:
UNIX_TIMESTAMP(`sys_create_time`) - LAG(UNIX_TIMESTAMP(`sys_create_time`)) OVER (ORDER BY `sys_create_time`)
LAG is a window function which allows you to grab the prior row. You'll need to talk with your CSM to get it turned on in your instance if you don't have it yet.
LEAD is similar but it grabs the next row (goes the opposite direction).
This will return the difference in time in seconds. If you want it in other units like minutes / hours / days then you can do some simple division.
To wrap it all up in a nice bow you can take the AVG of the difference to get the average.
AVG(UNIX_TIMESTAMP(`sys_create_time`) - LAG(UNIX_TIMESTAMP(`sys_create_time`)) OVER (ORDER BY `sys_create_time`))**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
I would take what @GrantSmith is suggesting, but do it in Magic ETL because it already supports the window function and you don't need to talk toy our CSM.
Just use the Rank & Window tile and use the LAG function as suggested so that your difference between occurrences will show as another column.**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.0
Thanks for the reply re LAG/LEAD functions.
Apparently those are already turned on in our system.
So, using your formula without the 'AVG' wrapper works to populate a column, BUT I cannot get it to aggregate.
If I add the AVG wrapper it breaks the function.
If I use it without the AVG, I can get it to show in a table view...
But I cannot get it to aggregate as a single number to give me average of all the rows...
- 7.6K All Categories
- 913 Connectors
- 241 Workbench
- 470 Transform
- 1.8K Magic ETL
- 60 SQL DataFlows
- 445 Datasets
- 24 Visualize
- 194 Beast Mode
- 2K Charting
- 6 Variables
- 14 Cards, Dashboards, Stories
- 1 Automate
- 348 APIs & Domo Developer
- 82 Apps
- 14 Predict
- 3 Jupyter Workspaces
- 11 R & Python Tiles
- 241 Distribute
- 59 Domo Everywhere
- 241 Scheduled Reports
- 14 Manage
- 35 Governance & Security
- 18 Product Ideas
- 1.1K Ideas Exchange
- Community Forums
- 15 Getting Started
- 1 Community Member Introductions
- 49 Community News
- 18 Event Recordings
- 579 日本支部