Custom Combo Chart

Newbie23
Newbie23 Member
in Charting

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.

image.png
image.png

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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!**
  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    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.

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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!**
  • Newbie23
    Newbie23 Member

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

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    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.

Categories