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

  • GrantSmith
    GrantSmith Indiana 🔴
    Accepted Answer

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

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.

  • GrantSmith
    GrantSmith Indiana 🔴

     

    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
  • 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. 

     

     

  • 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.

  • thanks a lot, it's working now

  • GrantSmith
    GrantSmith Indiana 🔴

    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.