Calculate time between incidents
Team,
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')
etc...
average of all
Help please?
Tim
Comments
-
Hi @tstimple
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.1 -
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...
Ideas...?
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 57 App Studio
- 40 Variables
- 685 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive