Cumulative SUM in Metabase

Get Help
1

Hi guys!
I'm new in the community.
I'm trying to create a table (from another raw table from my personal DB in AWS) that contains only 3 columns:

  • Month
  • Total
  • Total Cumulative
    I've created the table with the SQL editor for one reason: It seems that there's no native metabase function to get the cumulative sum function. So I wote it using the OVER function in SQL:
SELECT
  DISTINCT Month,
  SUM(Balance) OVER (ORDER BY Month) AS Balance Cumulative
FROM
  `030_011_MOVEMENTS`
WHERE 1=1
[[AND Month= {{Month}}]]
[[AND Country= {{Country}}]]

In addition, the raw table contains columns such as "Country", "Year", "Company". I want to implement filters to give users the opportunity to filter the previous mentioned columns and get a dynamic cumulative sum.
My solution involves using variables in Metabase SQL Editor, this allows to filter the mentioned columns and have a cumulative sum, but i can't select more than one value.
If I don't use the SQL Editor, I don't know how to calculate the cumulative.
I've tried more solutions, for example changed the WHERE clause in this way:

WHERE 1=1
[[AND Month IN ({{Month}})]]
[[AND Country IN ({{Country}})]]

But it still doesn't work.
Does anyone have the same problem? Possible solutions?

image
image693×472 14.6 KB

2

Can you use one of Metabase's functions? I've not used this, but cumulative sum is available.

2 Likes
3

I knew the functions but I had not considered them.
I have completely changed the logic of the support tables in SQL and I'm now using the functions you suggested, thank you very much!
You have given me a different point of view

2 Likes
4

It's unusual for me to take this approach! I'm much more of a 'fix it in the database' type.
Thanks for letting me know it works.

1 Like
5

Cumulative Sum would work to get the equivalent of the following query:
SUM(Balance) OVER (ORDER BY Month) AS Balance Cumulative

But what about if I were to get the sum per country. In other words, can I use Cumulative Sum to get the equivalent of the following query:
SUM(Balance) OVER (PARTITION BY country ORDER BY Month) AS Balance Cumulative

Thanks