How to calculate the total sum of a field in beast Mode

How can I calculate the total sum for a given row based on the condition. e.g.

for every G_Account No. I will like to calculate total Amount, for Transaction = 0 and Transaction = 1 aggregated by year:


I have used the formulala:

sum(sum(`Amount`)) over (partition by year(`Posting Date`)), however it correctly calculates for the year, but sums up both for Transaction 1 & 0.


Thank you

Answers

  • Sam1
    Sam1 ⚪️

    I think you'd need to do that with two separate beast modes; one like this:

    case when Transaction = 1 then
    sum(sum(`Amount`)) over (partition by year(`Posting Date`))
    end
    

    and one like this:

    case when Transaction = 0 then
    sum(sum(`Amount`)) over (partition by year(`Posting Date`))
    end
    

    You may need a third for the total year, but you have the formula for that already.

  • GrantSmith
    GrantSmith Indiana 🔴

    Hi @msharma since you’re wanting to do a conditional sum you need to use a case statement inside your sum to only select the values you want

    sum(sum(case when `Transaction` = 1 then `Amount` else 0 end)) over (partition by year(`Posting Date`))

    Repeat for transaction 0

  • msharma
    msharma ⚪️

    Thank you. It works.

    I have a follow up question.

    How to add hardcoded dates that the amount is summed for a period specified, even when the chard is filtered on for different dates.

    e.g. I will like to always get the sum of transactions Type 1 between the period of Jan-01-2020 and Dec-31-2021, even when the filter on the card is set for Jan-01-2021 and Feb-28-2021


    Thank you

  • GrantSmith
    GrantSmith Indiana 🔴

    You wouldn't be able to do that on the card because the filter happens before any beast mode is applied. You'd need to aggregate your data in an ETL before hand.