Can I use a Beast Mode for Null/Blank Values?

Options
kim_barragan0126
kim_barragan0126 Member
edited July 7 in Calculations & Variables (Beast Mode)

I have two issues I am trying to solve. Company123 has a budget of 706.34, but there was no $ for this week. The cell is blank, so my formula is not calculating. The Budget GP-GP should equal -706.34, but it is blank. Can I do a Beast Mode, or even something in the ETL would be okay?

Second issue is the 27,504.75 minus -77,584.85 should equal -50,080.10, but it is showing 105,089.60. Is there a better Beast Mode Formula I can use?

image.png image.png

Best Answers

  • GrantSmith
    GrantSmith Coach
    edited January 8 Answer ✓

    You can coalesce the values to default to 0 if they're null:

    SUM(COALESCE(`Budget GP`, 0)) - SUM(COALESCE(`Gross Profit`, 0))

    Edit: updating the formula.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • GrantSmith
    GrantSmith Coach
    edited January 8 Answer ✓

    You're subtracting a negative number so it ends up adding them together which is why you're getting 105 which is the span between your two numbers.

    It sounds like you want to take the difference of the budget and the absolute value of gross profit:

    SUM(COALESCE(`Budget GP`, 0)) - ABS(SUM(COALESCE(`Gross Profit`, 0))
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • kim_barragan0126
    kim_barragan0126 Member
    Answer ✓

    I figured it out - thanks!

Answers

  • GrantSmith
    GrantSmith Coach
    edited January 8 Answer ✓

    You can coalesce the values to default to 0 if they're null:

    SUM(COALESCE(`Budget GP`, 0)) - SUM(COALESCE(`Gross Profit`, 0))

    Edit: updating the formula.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • kim_barragan0126
    kim_barragan0126 Member

    Is this in ETL or Beast Mode?

    Thank you!

  • GrantSmith
    GrantSmith Coach
    edited January 8

    COALESCE will work in either a Beast Mode or a formula tile or custom group by aggregation logic in Magic ETL.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • kim_barragan0126
    kim_barragan0126 Member

    Thanks! Do you how I can fix the below. Appreciate the quick replies!

    Second issue is the 27,504.75 minus -77,584.85 should equal -50,080.10, but it is showing 105,089.60. Is there a better Beast Mode Formula I can use?

    image.png
  • kim_barragan0126
    kim_barragan0126 Member

    I am getting a Syntax Error when I use in Beast Mode.

    image.png
  • GrantSmith
    GrantSmith Coach
    edited January 8 Answer ✓

    You're subtracting a negative number so it ends up adding them together which is why you're getting 105 which is the span between your two numbers.

    It sounds like you want to take the difference of the budget and the absolute value of gross profit:

    SUM(COALESCE(`Budget GP`, 0)) - ABS(SUM(COALESCE(`Gross Profit`, 0))
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • GrantSmith
    GrantSmith Coach
    edited January 8
    https://community-forums.domo.com/main/discussion/comment/100120#Comment_100120

    You've got a COALESCE inside your COALESCE and you don't have your parenthesis properly terminated / closed. Try copying the forumla I posted directly above.

    My original formula had an issue. I've updated it.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • kim_barragan0126
    kim_barragan0126 Member
    Answer ✓

    I figured it out - thanks!

Categories