Need Help: Subtract CurrentRowDateTime - PreviousRowDateTime from same column
Hello, please help me create this in DOMO. Thank you.
Here are the columns needed:
- Alarm ID = (given)
- Alarm Handled Store Time = (given - sort oldest to newest)
- AHT = with formula, see screenshot below
AHT: currentRow - previousRow (from same column) (see detailed formula below)
the only condition is that: if the computed AHT = 0, then it will just copy the previous calculated AHT.
Answers
-
You can do this in Magic ETL, use the Rank & Window tile and use the Lag function to get the previous entry, which will create it as a new column. Next, add a Formula tile and subtract the two times. I would suggest using the unix_timestamp function on both datetimes and then subtracting. This will give you the number of seconds difference between the two times. You can then multiply by 60 to get to minutes and multiply by 60 again to get the number of hours.
Hope this helps.
**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 -
Hi, @MarkSnodgrass . Apologies, I have some changes with the columns and formulas:
Added column: TechNumber (given)
UPDATED SORTING:
TechNumber - A to Z
Alarm Handled Store Time - Oldest to NewestUpdated Formula shown below
Condition:
If TechNumber is the same with previous row, AHT will be computed,
AHT: (currentRow - previousRow)*24*60
If calculated AHT is zero, it will just copy the previously calculated AHT
ELSE 0
0 -
HI, @MarkSnodgrass, If it's not too much to ask, may we ask for your help in creating sample ETL / json file for it? Apologies, we haven't really touched ETL yet. We would greatly appreciate your help on this one so we can also review and see how it goes. Thank you very much.
0 -
Here is the JSON for the ETL that you would like to put together. You will have to connect the input dataset to your actual dataset and correct any column name differences, but I tried to match your screenshot.
{"contentType":"domo/dataflow-actions","data":[{"name":"DemoAlarmTime.xlsx","id":"986a7935-4eff-4fe7-878f-7fd7c062f2ce","type":"LoadFromVault","gui":{"x":108,"y":156,"color":3238043},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"91adafad-481e-4a95-8403-d11cf696ccbe","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"Rank & Window","id":"9e4c6190-59e3-4b51-9466-f096e1386bd8","type":"WindowAction","gui":{"x":228,"y":156},"dependsOn":["986a7935-4eff-4fe7-878f-7fd7c062f2ce"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"additions":[{"name":"Prev_AlarmTime","operation":{"type":"OFFSET","operationType":"LAG","column":"Alarm Handled Store Time","amount":"1"}}],"orderRules":[{"column":"Alarm ID","caseSensitive":false,"ascending":true}],"groupRules":[{"column":"Tech Name","caseSensitive":false}]},{"name":"Add Formula","id":"96748ab3-23aa-4407-8418-771dba6a6531","type":"ExpressionEvaluator","gui":{"x":348,"y":156},"dependsOn":["9e4c6190-59e3-4b51-9466-f096e1386bd8"],"removeByDefault":false,"notes":[],"expressions":[{"fieldName":"AHT","expression":"TIME_TO_SEC(TIMEDIFF(Alarm Handled Store Time, Prev_AlarmTime)) / 60\n","settings":null}]},{"name":"Filter Rows","id":"45465bd2-2354-4141-8d93-a81c4bd1e79d","type":"Filter","gui":{"x":468,"y":264},"dependsOn":["96748ab3-23aa-4407-8418-771dba6a6531"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"AHT","rightValue":{"value":"0","type":"DOUBLE"},"operator":"NE","andFilterList":[]}]},{"name":"Remove Duplicates","id":"4f95962d-8672-411a-a9df-4f046dab110f","type":"Unique","gui":{"x":588,"y":264},"dependsOn":["45465bd2-2354-4141-8d93-a81c4bd1e79d"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"fields":[{"name":"Tech Name","caseInsensitive":false},{"name":"Alarm Handled Store Time","caseInsensitive":false},{"name":"Prev_AlarmTime","caseInsensitive":false}]},{"name":"Join Data","id":"e0cbb736-9ab8-4a23-ac17-8b645b0fe1ad","type":"MergeJoin","gui":{"x":720,"y":156},"dependsOn":["96748ab3-23aa-4407-8418-771dba6a6531","4f95962d-8672-411a-a9df-4f046dab110f"],"removeByDefault":false,"notes":[],"joinType":"LEFT OUTER","step1":"96748ab3-23aa-4407-8418-771dba6a6531","step2":"4f95962d-8672-411a-a9df-4f046dab110f","keys1":["Tech Name","Alarm Handled Store Time"],"keys2":["Tech Name","Alarm Handled Store Time"],"schemaModification1":[],"schemaModification2":[{"name":"Alarm ID","rename":"Remove Duplicates.Alarm ID","remove":true},{"name":"Tech Name","rename":"","remove":true},{"name":"Alarm Handled Store Time","rename":"","remove":true},{"name":"Prev_AlarmTime","rename":"","remove":true},{"name":"AHT","rename":"Prev_AHT","remove":false}],"partitioningInputId":""},{"name":"Add Formula 1","id":"e08ffe31-a541-44c5-8213-e96c568bc691","type":"ExpressionEvaluator","gui":{"x":840,"y":156},"dependsOn":["e0cbb736-9ab8-4a23-ac17-8b645b0fe1ad"],"removeByDefault":false,"notes":[],"expressions":[{"fieldName":"Final_AHT","expression":"CASE when AHT = 0 and Prev_AHT is not null then Prev_AHT else AHT END","settings":null}]},{"name":"DemoAlartmTime","id":"db45f7aa-361e-4526-b80a-de22bd58d60d","type":"PublishToVault","gui":{"x":972,"y":156},"dependsOn":["e08ffe31-a541-44c5-8213-e96c568bc691"],"removeByDefault":false,"notes":[],"dataSource":{"name":"DemoAlartmTime"},"partitionIdColumns":[],"upsertColumns":[],"retainPartitionExpression":""}]}
Here is what it should look like.
You will want to use the Final_AHT field that is calculated at the end.
**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 -
Hi @MarkSnodgrass thank you very much for this. Will explore this and let you know if it works. 😊
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