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?