Beast Mode - Revenue from a negative to a positive

user031733

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

jaeW_at_Onyx

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

 

Cartergan

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

user031733

---

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

Cartergan

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

jaeW_at_Onyx

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

 

user031733

---

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