Skip to content

    • Beast Mode

    Beast Mode

    How to make a Positive value a Negative value

    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:

    1. SUM
    2. (CASE 
    3. WHEN DATE(`BatchTimestamp`) = DATE('2023-11-22') 
    4. AND YEAR(`Month`)=2024 
    5. AND `FinancialCategoryName` LIKE '%CAPEX%' 
    6. AND `Portfolio 2024_p`='Rejected' 
    7. THEN (`PlannedCost`)
    8. ELSE 0
    9. END)

    Possible?

    Thanks!

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • Coach
      Answer ✓

      Multiply your value by -1

      1. SUM
      2. (CASE 
      3. WHEN DATE(`BatchTimestamp`) = DATE('2023-11-22') 
      4. AND YEAR(`Month`)=2024 
      5. AND `FinancialCategoryName` LIKE '%CAPEX%' 
      6. AND `Portfolio 2024_p`='Rejected' 
      7. THEN (`PlannedCost`)*-1
      8. ELSE 0
      9. END)
      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**

    Answers

    • Coach
      Answer ✓

      Multiply your value by -1

      1. SUM
      2. (CASE 
      3. WHEN DATE(`BatchTimestamp`) = DATE('2023-11-22') 
      4. AND YEAR(`Month`)=2024 
      5. AND `FinancialCategoryName` LIKE '%CAPEX%' 
      6. AND `Portfolio 2024_p`='Rejected' 
      7. THEN (`PlannedCost`)*-1
      8. ELSE 0
      9. END)
      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**
    • Member

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

      Thanks!

    • 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)
    • 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:

      1. MAX(CASE 
      2. WHEN `Portfolio 2024_p`='Unplanned' 
      3. AND (`First Year of Impact_p` = '2024' OR `First Year of Impact_p` IS NULL)
      4. THEN `Yearly Total RUN Costs (CHF)_p`*-1
      5. ELSE 0
      6. 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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories