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.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 299 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 681 Automate
- 175 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 9 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
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive