Best Of

@carthur On your second formula, you are getting an error because the logic needs to be within a case statement. I'd suggest doing this instead:

case 
  when YEAR(`ORDER_DATE`) = YEAR(DATE_SUB(CURDATE(),interval 1 year)) 
    and ORDER_DATE <= DATE_SUB(CURDATE(),interval 1 year)
  then 'Include' 
  else 'Exclude'
end

Your STR_TO_DATE format is not quite correct.

The first one isn't including the dashes that you've concatenated in there. The second is including commans which aren't included.

Try something like this:

CASE WHEN `ORDER_DATE` >= STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE()-1), '-01-01'), '%Y-%m-%d')
AND 
`ORDER_DATE` <= STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE())-1, '-', MONTH(CURRENT_DATE()), '-', DAY(CURRENT_DATE())), '%Y-%m-%d')
THEN 'YES'
ELSE 'NO'
END

Try this:

SUM(CASE WHEN `Data` in ('Goal','Pipeline','in P&L') THEN `Total` ELSE 0 END)

This should get the total you want.

Do you want to sum them separately and then add them together? Or do you want them as 3 separate calculations? If you want them as 3 separate calculations, then you would create 3 different beast modes, one for each.

It may be easiest if you explain what it is you are trying to achieve. What are you trying to display?