Beast Mode - Revenue from a negative to a positive

user031733
user031733 Member
in Magic ETL

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

     

    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"

Answers

  • Cartergan
    Cartergan Contributor

    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
    user031733 Member
    @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
    Cartergan Contributor

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

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    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`

     

    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"
  • user031733
    user031733 Member
    @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! 

Categories