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)
- TechName = (given)
- Alarm Handled Store Time = (given)
- Average Handling Time (AHT) = see detailed formula and condition below
Sorting:
TechName - A to Z
Alarm Handled Store Time - Oldest to Newest
Condition:
If TechName 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
Best Answer
-
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
Answers
-
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.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive