Create a summary number as calculation aggregate sum expense minus aggregate sum reimbursements

ISD_Aman · February 2022

Hello,

I am trying to create a beast mode calculation to show balance remaining after (sum of all expenditure) - (sum of all reimbursements).

here is my current calculation.

(case when `Expense Type` = 'Expenditure' then sum(`Account Balance`) else 0 end)

-

(case when `Expense Type` = 'Reimbursement' then sum(`Account Balance`) else 0 end)

The result I get is a sum of all (expenditure + reimbursements) rather than the intended (sum of all expenditures) - (sum of all reimbursements).

Thanks in advance for help.

mhouston · February 2022

You want to put your sum() around your case statement:

sum(case when `Expense Type` = 'Expenditure' then `Account Balance` else 0 end)

-

sum(case when `Expense Type` = 'Reimbursement' then `Account Balance` else 0 end)

ISD_Aman · February 2022

That actually worked. Thanks.

Magic ETL