Case Statement Help - Condition not being passed

Reply
Highlighted
Yellow Belt

Case Statement Help - Condition not being passed

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


Accepted Solutions
Highlighted
Red Belt

@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)


**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post


All Replies
Highlighted
Brown Belt

Some suggestions:

 

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


-----------------
Chris
Highlighted
Red Belt

 

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


**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
Highlighted
Yellow Belt

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. 

 

 

Highlighted
Yellow Belt

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.

Highlighted
Red Belt

@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)


**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post

Highlighted
Yellow Belt

thanks a lot, it's working now

Highlighted
Red Belt

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.



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
Announcements
Domopalooza 2021 Call for Presenters: We want to hear how Domo is revolutionizing the way you do business!

Click here to submit your story.