Aggregate Running Total
I am trying to create a running total chart that calculates the aggregate running total as the day goes on. However, using the running total chart in DOMO, it sums up the amounts per time period rather than take aggregate amounts. I am hoping someone out there has come up with a solution for the way the chart is built.
To illustrate the problem assume I have a data set that has these amounts:
Time | Amount1 | Amount2 |
10:00 AM | 2 | 1 |
10:15 AM | 2 | 1 |
10:30 AM | 3 | 1 |
10:45 AM | 4 | 1 |
and I've written a beast mode calc that is Sum(Amount1)/Sum(Amount2).
I put it into a running total chart and DOMO give me this:
However, what I want DOMO to do is sum the amounts first, then divide so that I get a chart that looks like this:
Has anyone come across this problem before and possibly have a solution?
Thanks.
Comments
-
I'm not sure if it's possible to do what you're wanting in the running total card.
What I would do is use a SQL transform like so to SUM the values ahead of time. Here's a sample I tested using your numbers:
SELECT a.`Time`, (SELECT SUM(b.`Amount1`) FROM testing as b WHERE b.`Time` <= a.`Time`) AS 'Sum1', (SELECT SUM(b.`Amount2`) FROM testing as b WHERE b.`Time` <= a.`Time`) AS 'Sum2'
FROM testing as aJust replace the table name with your own.
I was able to use the results from this, create a basic line card with a BeastMode that was just `Sum1`/`Sum2`
That produced the chart you were looking for.
Hopefully that will help.
Sincerely,ValiantSpur
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.2 -
Thanks for the tip. Given the complexity of the data (multiple locations, times) I was trying to avoid doing the calculation ahead of time in SQL. But if that's what I need to so be it.
Thanks.
0 -
very helpful thank you! This is just what I was looking for!
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
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 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