Dashboard Table using previous day values in calculation
Hi,
I need to create a Dashboard that calculates oil usage for Engines. Below is the excel table with calculation I need to recreate in Domo. Basically need to take the tank level and oil added to day tank and engine from day before and subtract today's tank level to get daily usage.
Also sometimes there wont always be a previous day reading. There could be a day or 2 between so need to have logic that takes that into account. Could be values on 4/4, 4/5, then not til 4/7 so on 4/7 would need to use 4/5 values since that was last day measured.
Could someone help explain how could do this? Thanks!
Best Answer
-
I would probably do this in Magic ETL. You can use a window function tile to lead the next day's tank level, which would allow you to do the math in your screenshot.
I think you can also use lead functions in Beast Modes but I haven't used them there as much.
1
Answers
-
I would probably do this in Magic ETL. You can use a window function tile to lead the next day's tank level, which would allow you to do the math in your screenshot.
I think you can also use lead functions in Beast Modes but I haven't used them there as much.
1 -
@TMonty0319 here is how you could do this in a beast mode. Lag will default to the last available date.
For example you can see that this dataset skips days, but the beast mode still shows the data from the last available date.
(LAG(SUM(day_tank_value),1) over (order by date asc) + LAG(SUM(oil_added_tank),1) over (order by date asc) + LAG(SUM(oil_added_engine),1) over (order by date asc)) -SUM(value)
You could then use a beast mode like the above to calculate your desired value.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**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