How to make a Positive value a Negative value

PJG
PJG Member
in Beast Mode

Sounds like ABS can make a Negative value a Positive value, but how can I do the opposite?

Need the result from this Beast Mode to be converted to a negative value:

SUM
(CASE 
WHEN DATE(`BatchTimestamp`) = DATE('2023-11-22') 
AND YEAR(`Month`)=2024 
AND `FinancialCategoryName` LIKE '%CAPEX%' 
AND `Portfolio 2024_p`='Rejected' 
THEN (`PlannedCost`)

ELSE 0

END)

Possible?

Thanks!

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Multiply your value by -1

    SUM
(CASE 
WHEN DATE(`BatchTimestamp`) = DATE('2023-11-22') 
AND YEAR(`Month`)=2024 
AND `FinancialCategoryName` LIKE '%CAPEX%' 
AND `Portfolio 2024_p`='Rejected' 
THEN (`PlannedCost`)*-1
ELSE 0
END)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Multiply your value by -1

    SUM
(CASE 
WHEN DATE(`BatchTimestamp`) = DATE('2023-11-22') 
AND YEAR(`Month`)=2024 
AND `FinancialCategoryName` LIKE '%CAPEX%' 
AND `Portfolio 2024_p`='Rejected' 
THEN (`PlannedCost`)*-1
ELSE 0
END)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • PJG
    PJG Member

    Ah, as simple as that, and I was looking for a function to do it! :)

    Thanks!

  • nmizzell
    nmizzell Contributor

    1. You can use a nested case statement:
    3. SUM
    4. (CASE
    5. WHEN DATE(`BatchTimestamp`) = DATE('2023-11-22')
    6. AND YEAR(`Month`)=2024
    7. AND `FinancialCategoryName` LIKE '%CAPEX%'
    8. AND `Portfolio 2024_p`='Rejected'
    9. THEN
    10. (case when
    11. `PlannedCost` >=0 then `Planned Cost` * -1
    12. else `Planned Cost` end
    13. )
    14. ELSE 0
    15. END)

  • PJG
    PJG Member
    edited September 2024

    Hi all,

    Follow up to this. I took a before and after of my card to ensure it was accurately converting the positive numbers to negative numbers, and in most cases, it worked as intended. However, for certain Beast Modes, it is invoking the 0 instead of converting the number to a negative. Here is one example:

    MAX(CASE 
    
WHEN `Portfolio 2024_p`='Unplanned' 
    
AND (`First Year of Impact_p` = '2024' OR `First Year of Impact_p` IS NULL)
    
THEN `Yearly Total RUN Costs (CHF)_p`*-1
    
ELSE 0
    
END)

    As soon as I added the *-1 it changed the result from 850,000 to 0. Why is the ELSE 0 being invoked?

    Thanks

Categories