SUM with multiple conditions

user066906

Hello everyone,

I am having trouble with a DOMO Beast mode sum Calculation. Below you will find my existing calculation. I am looking to add the value of all records that have a date to Customer in column A and exclude those records in column B that have a payment date. Essentially I am looking to calculate the value of invoices sent to the Customer and of those who has not submitted payment yet.

Issue: It appears that I am still adding values to my total when payment dates are present.

Can someone please help me to improve my beast mode calculation?

SUM(

 CASE

  WHEN ` Date To Customer` IS NOT NULL AND `Payment Date` IS NULL THEN (`USD`)

 END

)

GrantSmith

Hi @user066906

Typically when I have a complex beastmode that isn't functioning as expected I'll break it down into different beast modes and toss them onto a table card to help investigate each portion.

Does your payment check return what you're expecting on it's own without the SUM for each record?

CASE WHEN `Payment Date` IS NULL THEN `USD` END

CASE WHEN `Date To Customer` IS NOT NULL THEN `USD` END

CASE WHEN `Date To Customer` IS NOT NULL AND `Payment Date` IS NULL THEN `USD` END

MarkSnodgrass

Agree with @GrantSmith . I would also add in an ELSE statement into the test logic and the final logic to ensure that it is evaluating the NULLS correctly. You could have blanks instead of nulls which would cause this statement to not return the results you are expecting. If you are actually have blanks you could do something like this:

SUM(
 CASE
  WHEN ` Date To Customer` '1/1/1900'  AND (`Payment Date` IS NULL OR TRIM(`Payment Date`) = '')  THEN (`USD`)
ELSE 0
 END
)

Hope this helps.

jaeW_at_Onyx

@user066906 are your invoices and payment records on the same row? or are they spread across rows? if it's spread across rows these solutions we're proposing won't work for you... so ... make sure to double check.

@MarkSnodgrass 's checks for is null OR is blank are perfect, but just do the opposite check as proposed by @GrantSmith for the dateToCustomer

sum(
case 
when (`date to customer` is not null AND trim(`date to customer`) <> '') AND (`Payment Date` IS NULL OR TRIM(`Payment Date`) = '')  THEN `USD`
end
)

Note the difference between AND and OR.

most of the time, you don't need ELSE 0 when you're aggregating.

user066906

@jaeW_at_Onyx

Hi, I tried your solution and I still receive the same results. It looks like I am still pulling in blank values for 'date to Customer' and payment dates with values. I have tried many variations of the suggestions and still seem to get the same results.

All of the data for each record is in the same row.

Do you have any other ideas?

jaeW_at_Onyx

@user066906

the code that everyone gave you is correct. the problem is in your understanding of your data and how you're testing your data.

if you can't identify your data with s not null and empty string, then it must contain SOMETHING that you aren't testing for. figure out what the something is.

alternatively, post a sample of your data.