Beast Mode - Revenue from a negative to a positive
Hi,
Novice beast mode user here and currently our data flow is showing revenue as a negative number and expenses as positive numbers, however I would like revenue to also show as positive. I tried the below beast mode calculation below, but it didn't work. Any advice on what beast mode calc, or other way, to turn revenue into a positive number?
IFNULL(`Department Name` = Revenue, ABS(`Transaction Currency Amount`))
Department name is where our revenue flows under and transaction currency amount is the value I want it in.
Thanks
Best Answer
-
Case when `Department Name` = "Revenue" then ABS(`Transaction Currency Amount`) End
By using double quotes, ", around Revenue, it's interpreting it as a column name instead of text.
I believe you meant:
Case when `Department Name` = 'Revenue' then ABS(`Transaction Currency Amount`) End
Because you want to invert the sign of any transaction where the Department name is Revenue, I invite you to write the beast mode as
sum(
Case when `Department Name` = 'Revenue' then -1 * `Transaction Currency Amount`
ELSE `Transaction Currency Amount`
EndIf I'm honest, when i build PNLs, usually I try to define the department's who's sign i need to invert using a Lookup table (webform). Here's an example in a youtube video i created, you just need to add isInvert to the Lookup Table.
https://www.youtube.com/watch?v=YgevJkjeFqw&list=PLUy_qbtzH0S4CkHBUvpOVpLNJluk6upOn&index=17&t=12s
For your use case, my Lookup table would be:
[Department Name] , [IsInvert]
With values
Revenue -1
COGS -1
Assets 1
Liabilities 1
That way I can JOIN my webform to the Transactions and make my beast mode:
isInvert * `Transaction Currency Amount`
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Answers
-
It seems you may need to include a Case statement into the beast mode since you are checking for when the Department Name equals a value. Something along the lines of:
CASE WHEN 'Department Name' = "Revenue" THEN ABS('Transaction Currency Amount') END
0 -
@Cartergan wrote:It seems you may need to include a Case statement into the beast mode since you are checking for when the Department Name equals a value. Something along the lines of:
CASE WHEN 'Department Name' = "Revenue" THEN ABS('Transaction Currency Amount') END
Thanks for you response. Unfortunately I received the error that "A column in this calculation did not exist." I am not sure how that's possible when I am using both referenced in the equation in the card I am putting together.
Case when `Department Name` = "Revenue" then ABS(`Transaction Currency Amount`) End
Thoughts?
0 -
Would you mind sharing a screen shot of the column names in your dataset?
0 -
Case when `Department Name` = "Revenue" then ABS(`Transaction Currency Amount`) End
By using double quotes, ", around Revenue, it's interpreting it as a column name instead of text.
I believe you meant:
Case when `Department Name` = 'Revenue' then ABS(`Transaction Currency Amount`) End
Because you want to invert the sign of any transaction where the Department name is Revenue, I invite you to write the beast mode as
sum(
Case when `Department Name` = 'Revenue' then -1 * `Transaction Currency Amount`
ELSE `Transaction Currency Amount`
EndIf I'm honest, when i build PNLs, usually I try to define the department's who's sign i need to invert using a Lookup table (webform). Here's an example in a youtube video i created, you just need to add isInvert to the Lookup Table.
https://www.youtube.com/watch?v=YgevJkjeFqw&list=PLUy_qbtzH0S4CkHBUvpOVpLNJluk6upOn&index=17&t=12s
For your use case, my Lookup table would be:
[Department Name] , [IsInvert]
With values
Revenue -1
COGS -1
Assets 1
Liabilities 1
That way I can JOIN my webform to the Transactions and make my beast mode:
isInvert * `Transaction Currency Amount`
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
@jaeW_at_Onyx wrote:Case when `Department Name` = "Revenue" then ABS(`Transaction Currency Amount`) End
By using double quotes, ", around Revenue, it's interpreting it as a column name instead of text.
I believe you meant:
Case when `Department Name` = 'Revenue' then ABS(`Transaction Currency Amount`) End
Because you want to invert the sign of any transaction where the Department name is Revenue, I invite you to write the beast mode as
sum(
Case when `Department Name` = 'Revenue' then -1 * `Transaction Currency Amount`
ELSE `Transaction Currency Amount`
EndIf I'm honest, when i build PNLs, usually I try to define the department's who's sign i need to invert using a Lookup table (webform). Here's an example in a youtube video i created, you just need to add isInvert to the Lookup Table.
https://www.youtube.com/watch?v=YgevJkjeFqw&list=PLUy_qbtzH0S4CkHBUvpOVpLNJluk6upOn&index=17&t=12s
For your use case, my Lookup table would be:
[Department Name] , [IsInvert]
With values
Revenue -1
COGS -1
Assets 1
Liabilities 1
That way I can JOIN my webform to the Transactions and make my beast mode:
isInvert * `Transaction Currency Amount`
This solution worked! Thanks for all your help, I appreciate it!
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 98 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 712 Beast Mode
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 104 Community Announcements
- 4.8K Archive