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
Best Answer
-
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 Agree or Like below**
**Did this solve your problem? Accept it as a solution!**3
Answers
-
Some suggestions:
Could there be trailing spaces in transaction_type? You could try `transaction_type` like 'Actuals%'. Also verify the case of transaction_type.
-----------------
Chris1 -
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 Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
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.
1 -
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
ENDNow 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.
1 -
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 Agree or Like below**
**Did this solve your problem? Accept it as a solution!**3 -
thanks a lot, it's working now
1 -
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 Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 60 App Studio
- 41 Variables
- 687 Automate
- 177 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 126 Manage
- 123 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive