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

kim_barragan0126

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?

GrantSmith

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.

GrantSmith

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))

kim_barragan0126

I figured it out - thanks!

GrantSmith

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.

kim_barragan0126

Is this in ETL or Beast Mode?

Thank you!

GrantSmith

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

kim_barragan0126

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?

kim_barragan0126

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

GrantSmith

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))

GrantSmith

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.

kim_barragan0126

I figured it out - thanks!