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

Options
ISD_Aman
ISD_Aman Member
in Magic ETL

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.

Tagged:

Answers

  • mhouston
    mhouston Contributor

    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
    ISD_Aman Member

    That actually worked. Thanks.

Categories