Beast Mode - Revenue from a negative to a positive

Reply
Highlighted
White Belt

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


Accepted Solutions
Highlighted
Major Red Belt

Re: Beast Mode - Revenue from a negative to a positive

@Cartergan 

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`
End

 

If 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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post

Build a profit in loss statement in #Domo using out of the box features. "Is there any option or way to add a row heading and a blank row in pivot table." ##...

All Replies
Highlighted
Brown Belt

Re: Beast Mode - Revenue from a negative to a positive

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
Highlighted
White Belt

Re: Beast Mode - Revenue from a negative to a positive


@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?

Highlighted
Brown Belt

Re: Beast Mode - Revenue from a negative to a positive

Would you mind sharing a screen shot of the column names in your dataset? 

Highlighted
Major Red Belt

Re: Beast Mode - Revenue from a negative to a positive

@Cartergan 

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`
End

 

If 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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post

Build a profit in loss statement in #Domo using out of the box features. "Is there any option or way to add a row heading and a blank row in pivot table." ##...
Highlighted
White Belt

Re: Beast Mode - Revenue from a negative to a positive


@jaeW_at_Onyx wrote:

@Cartergan 

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`
End

 

If 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! 

Announcements
Stand out in the Dojo: Choose your own recognizable username and add a unique profile photo in your profile settings.