Case Statement Help - Condition not being passed

mamedu

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

GrantSmith

@mamedu-

Try this:

 

SUM(CASE WHEN `transaction_type` = 'Actuals' AND
(
( YEAR(`transaction_date`) = YEAR(CURRENT_DATE()) AND MONTH(`transaction_date`) < 7 ) OR
( YEAR(`transaction_date`) = YEAR(CURRENT_DATE())-1 AND MONTH(`transaction_date`) >= 7 )
)
THEN
`net`
ELSE 0
END)

cwolman

Some suggestions:

 

Could there be trailing spaces in transaction_type?  You could try `transaction_type` like 'Actuals%'.  Also verify the case of transaction_type.

GrantSmith

 

Have you confirmed the value of 'Actuals' in your data? Does it have any trailing whitespace?

 

Instead of = 'Actuals' have you tried doing `transaction_type` LIKE '%Actuals%'

 

Here's a rewrite of your logic but it appears correct glancing over it.

CASE WHEN `transaction_type` = 'Actuals' AND ((YEAR('transaction_date`) = YEAR(CURRENT_DATE()) AND MONTH(`transaction_date`) < 7) OR (YEAR('transaction_date`) = YEAR(CURRENT_DATE())-1 AND MONTH(`transaction_date`) >= 7)) THEN
    SUM(`net`)
ELSE
    0
END

mamedu

The transaction_type does contain a value, 'Actuals'. There's only 2 values in the transaction_type field, 'Actuals' & 'Budget' and I've tried both and still not working. I've tried the 'like' method as well and it's still not working. 

 

 

mamedu

Hi @GrantSmith , I tried the logic you recommended and I'm still getting the same problem but I did notice something. So the calculation is:

CASE WHEN `transaction_type` = 'Actuals' AND
(
( YEAR(`transaction_date`) = YEAR(CURRENT_DATE()) AND MONTH(`transaction_date`) < 7 ) OR
( YEAR(`transaction_date`) = YEAR(CURRENT_DATE())-1 AND MONTH(`transaction_date`) >= 7 )
)
THEN
SUM(`net`)
ELSE 0
END

 

Now in my table view, I have 3 fields, Year, Revenue, Test field. Since the aggregation is on Sum, the values are showing as $100MM (25M are suppose to be for Actuals & the other value Budget should have 75M). Now when I add transaction_type field into the view, then I see the breakdown as:

 

Year  | Net          | Test Field

2020 | 100M       | 100M

 

After adding Transaction_type into the view

Year  | Net        | Test Field | Transaction Type

2020 | 25M       | 25M         | Actuals

2020 | 75M       | 0              | Budget

 

This confuses me more because it seems when you add transaction type field into the view, the logic is working but when it's not there, it doesn't work.

GrantSmith

@mamedu-

Try this:

 

SUM(CASE WHEN `transaction_type` = 'Actuals' AND
(
( YEAR(`transaction_date`) = YEAR(CURRENT_DATE()) AND MONTH(`transaction_date`) < 7 ) OR
( YEAR(`transaction_date`) = YEAR(CURRENT_DATE())-1 AND MONTH(`transaction_date`) >= 7 )
)
THEN
`net`
ELSE 0
END)

mamedu

thanks a lot, it's working now

GrantSmith

Great!

 

To clarify here you needed to filter the data before applying the SUM function. In your original one it was essentially taking the sum of all the records if one of them was for Actuals.

 

Moving the SUM to the outside of your case statement caused the data to be filtered first and giving you then desired result.