How to make a Positive value a Negative value

PJG

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!

GrantSmith

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)

GrantSmith

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)

PJG

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

Thanks!

nmizzell

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

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