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

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    Accepted Answer

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

     

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

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

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


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