Case Statement Help - Condition not being passed

mamedu
mamedu Member
in Magic ETL

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

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    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)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • cwolman
    cwolman Contributor

    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
  • GrantSmith
    GrantSmith Coach

     

    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!**
  • mamedu
    mamedu Member

    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
    mamedu Member

    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
    GrantSmith Coach
    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)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • mamedu
    mamedu Member

    thanks a lot, it's working now

  • GrantSmith
    GrantSmith Coach

    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!**

Categories