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('2023-11-22') 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('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!**0
Answers
-
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!**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('2023-11-22')
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.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive