Custom Combo Chart
Hello, I need some help adding a line to my chart and some calculations.
In the chart below (first chart from domo), I have monthly KGPM (Kilograms per million), I want to add a line that will show the KGPD YTD (year to date), something similar to chart 2 below in excel. Now, in order to add the line I need to do some calculations, possibly add a beast mode, but I can not come up with the idea on how to do the calculation.
The chart on excel, is adding the previous month, so for YTD KgPM for June 2022 is adding the Qty rejected for April, May and June, divided by the sum of the qty received in April, May and June, and so on for July, August, …. So for my domo chart, I want to add the line but before I need to figure out how to do the calculations. Any ideas.
Thanks.
Best Answers
-
You can do a running total over time using a window function. Something like this in a beast mode might work for you.
SUM(SUM(`field`)) OVER (PARTITION BY `fiscal_year` ORDER BY `date`)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
This is based on the MySQL window functions, with the exception that you need double aggregation functions for them to work:
partition_clause
: APARTITION BY
clause indicates how to divide the query rows into groups. The window function result for a given row is based on the rows of the partition that contains the row. IfPARTITION BY
is omitted, there is a single partition consisting of all query rows.There are ton of really good videos about this in different channels you can refer to that might help. Here's one as an example.
0
Answers
-
You can do a running total over time using a window function. Something like this in a beast mode might work for you.
SUM(SUM(`field`)) OVER (PARTITION BY `fiscal_year` ORDER BY `date`)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
can you be a little more specific, how the "partition by" works.
0 -
This is based on the MySQL window functions, with the exception that you need double aggregation functions for them to work:
partition_clause
: APARTITION BY
clause indicates how to divide the query rows into groups. The window function result for a given row is based on the rows of the partition that contains the row. IfPARTITION BY
is omitted, there is a single partition consisting of all query rows.There are ton of really good videos about this in different channels you can refer to that might help. Here's one as an example.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive