Best Of

@carthur You don't actually need a GROUP BY clause in beast mode the same way that you would in SQL because your beast mode should group by whatever dimension is used in your card. If I'm interpreting your examples correctly, you are trying to sum the amount column for all locations and multiply the amount for ATL by 10. In that case your beast mode should looks like this:

sum(case when `location` = 'ATL' then `amount` * 10 else `amount` end)

In general, it's better to put case statements inside aggregate functions instead of the other way around since that often results in granularity issues.

You need to wrap your entire case statement in SUM instead of having your condition be the sum.

It's evaluating each row at a time and then aggregating when you want to aggregate after evaluating all the rows.

SUM(CASE WHEN
…
…
THEN
`INVOICE_AMT`
END
)

In addition to @GrantSmith 's suggestion of moving the SUM to the outside, you can simplify your current year evaluation by doing something like this:

YEAR(invoicedate) = YEAR(CURRENT_DATE()) AND invoicedate < = CURRENT_DATE()

For the previous year you can again just use the YEAR function like this:

YEAR(invoicedate) = YEAR(CURRENT_DATE())-1

Hope this helps.

While this solves the particular case keep in mind that this solution is not easy to scale. I'd recommend using a Webform datasource to track Team changes with start and End Date, and then do some ETL to determine the team that person should be in for each entry based on it. Is more work, but that would allow you to have better control on this and can cover more complex scenarios should this be something that can happen in the future.

The Webform basically would have 3 columns: Team Member Id, Team and JoinDate, you could have a LeaveDate but not mandatory as it'll be determined by the date of the next entry in the list via the same ETL and you wouldn't risk having gaps (of course this means you'll need to populate this the first time for all employees with their current value). Just an idea you might want to consider exploring in the future.