Filter by column subtraction

Get Help
1

Hi All

I am trying to filter a column (Type) with 4 types (card-out, card-out, bank-in, bank-out).

Goal: Sum of card-out + bank-out , and Sum of card-in + bank-in, and after subtract both to see if there is any amount remaining within the account.

For that, I am creating a custom column. where the formula I'm applying is:

Sum([Type], " card-out" AND [Type], "bank-out") - Sum([Type], " card-in" AND [Type], "bank-in")

and Metabase give me an error. Do anyone know they way to build queries on metabase?

Thanks beforehand!

2

Hi @MarcosIn
Have a look at the expression documentation, since you cannot use Sum like that:
https://www.metabase.com/docs/latest/users-guide/expressions.html
Perhaps something like this would work:

SumIf([Amount], [Type] = "card-out" OR [Type] = "bank-out") - SumIf([Amount], [Type] = "card-in" OR [Type] = "bank-in")
3

Thanks but doesnt work. Les try another way

First I am looking for splitting the column that contains the 4 types into 2 columns (The Ins, and the Outs).

Is that possible?

Thanks

4

@MarcosIn I don't understand. Try including an example of your data or how you would do it in SQL.

5

Yes sure.

I want to split Type column into two.

The first one:

SELECT SUM(Type)
FROM Transactions,
WHERE Type="card-incoming'
AND Type='bank-incoming'
GROUP BY Amount;
The second one:

SELECT SUM(Type)
FROM Transactions,
WHERE Type="card-outgoing'
AND Type='bank-outgoing'
GROUP BY Amount;

Pretty much, what I want is to compare the difference between both columns to see if there is any amount of money remaining.

Thanks!

6

@MarcosIn Your query doesn't make sense. You are summing by a string. But it's exactly what I included previously a Custom Expression example for.
Otherwise just use SQL if you need something more advanced than what you can do in the GUI.

7

Sorry, would be like this:

The first one:

SELECT Type, SUM(Amount)
FROM Transactions,
WHERE Type="card-incoming'
AND Type='bank-incoming'
;

The second one:

SELECT Type, SUM(Amount)
FROM Transactions,
WHERE Type="card-outgoing'
AND Type='bank-outgoing'
;

8

@MarcosIn That's exactly what my example did. If you need a query generated exactly like you show, then you'll need to do it in SQL.

1 Like