Hi All,
I've been struggling with this absurd problem when creating a beast mode calculation using case statement. Basically I'm trying to pass 2 conditions into my case statement but for some reason, even after validating the formula, only 1 condition is passing. My calculation is:
(CASE WHEN YEAR(`transaction_date`) = YEAR(CURRENT_DATE()) AND `transaction_type` = 'Actuals' THEN
(CASE WHEN MONTH(`transaction_date`) < 7 THEN SUM(`net`) ELSE 0 END)
WHEN YEAR(`transaction_date`) = YEAR(CURRENT_DATE())-1 AND `transaction_type` = 'Actuals' THEN
(CASE WHEN MONTH(`transaction_date`) >= 7 THEN SUM(`net`) ELSE 0 END)
ELSE 0 END)
There's results from this calculation are filtering on the dates but not on the transaction_type. I've tried positioning the transaction_type condition all around the case statement but it keeps failing. Examples:
(CASE WHEN YEAR(`transaction_date`) = YEAR(CURRENT_DATE()) THEN
(CASE WHEN MONTH(`transaction_date`) < 7 AND `transaction_type` = 'Actuals' THEN SUM(`net`) ELSE 0 END)
WHEN YEAR(`transaction_date`) = YEAR(CURRENT_DATE())-1 THEN
(CASE WHEN MONTH(`transaction_date`) >= 7 AND `transaction_type` = 'Actuals' THEN SUM(`net`) ELSE 0 END)
ELSE 0 END)
---------------------------
(CASE when `transaction_type` in ('Actuals') then
(CASE WHEN YEAR(`transaction_date`) = YEAR(CURRENT_DATE()) THEN
(CASE WHEN MONTH(`transaction_date`) < 7 THEN SUM(`net`) ELSE 0 END)
WHEN YEAR(`transaction_date`) = YEAR(CURRENT_DATE())-1 THEN
(CASE WHEN MONTH(`transaction_date`) >= 7 THEN SUM(`net`) ELSE 0 END)
ELSE 0 END)
end )
---------------------------
I can't figure out what I'm doing wrong in this case statement and why the condition isn't passing. Please if anyone can help or advise on the solution, I appreciate it.
Thanks