Custom Combo Chart

Options
Member

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.

• Coach
Options

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`)
```

**Did this solve your problem? Accept it as a solution!**
• Coach
Options

This is based on the MySQL window functions, with the exception that you need double aggregation functions for them to work:

`partition_clause`: A `PARTITION 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. If `PARTITION 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.

• Coach
Options

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`)
```

**Did this solve your problem? Accept it as a solution!**
• Member
Options

can you be a little more specific, how the "partition by" works.

• Coach
`partition_clause`: A `PARTITION 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. If `PARTITION BY` is omitted, there is a single partition consisting of all query rows.