How to make a Positive value a Negative value
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('20231122') AND YEAR(`Month`)=2024 AND `FinancialCategoryName` LIKE '%CAPEX%' AND `Portfolio 2024_p`='Rejected' THEN (`PlannedCost`)
ELSE 0
END)
Possible?
Thanks!
0
Best Answer

Multiply your value by 1
SUM (CASE WHEN DATE(`BatchTimestamp`) = DATE('20231122') 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!**0
Answers

Multiply your value by 1
SUM (CASE WHEN DATE(`BatchTimestamp`) = DATE('20231122') 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!**0 
Ah, as simple as that, and I was looking for a function to do it! :)
Thanks!
0 
 You can use a nested case statement:
SUM
(CASE
WHEN DATE(`BatchTimestamp`) = DATE('20231122')
AND YEAR(`Month`)=2024
AND `FinancialCategoryName` LIKE '%CAPEX%'
AND `Portfolio 2024_p`='Rejected'
THEN
(case when
`PlannedCost` >=0 then `Planned Cost` * 1
else `Planned Cost` end
)
ELSE 0
END)
0 
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
0
Categories
 All Categories
 1.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 292 Workbench
 4 Cloud Amplifier
 8 Federated
 2.8K Transform
 95 SQL DataFlows
 602 Datasets
 2.1K Magic ETL
 3.7K Visualize
 2.4K Charting
 695 Beast Mode
 43 App Studio
 39 Variables
 658 Automate
 170 Apps
 441 APIs & Domo Developer
 42 Workflows
 5 DomoAI
 32 Predict
 12 Jupyter Workspaces
 20 R & Python Tiles
 386 Distribute
 111 Domo Everywhere
 269 Scheduled Reports
 6 Software Integrations
 113 Manage
 110 Governance & Security
 8 Domo University
 30 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 98 Community Announcements
 Domo Community Gallery
 4.8K Archive