Sum Where Field Is Null in Data Model

[Anonymous]

Below is my data model:

If have created a straight table with the following fields:

I want to do some basic DQ calculations. Such as SUM AMOUNT USD when Contact ID is NOT NULL and Lead ID IS NULL.

Not having much luck after some research.

Any help is appreciated.

[swuehl]

You can't select NULL, so the set modifier can't work.

You would need to select in another field that shows a unique value where the records fulfills the requirement.

See also

Excluding values in Set Analysis

[Anonymous]

This works. You are the best. Any insight into how you came to this answer?

[sunny_talwar]

From your screenshot I saw that you were looking at Contact ID and Lead ID against Opportunity ID and as Stefan mentioned that you need a non-null unique field to check against the nullness of another field.

[Anonymous]

thank you stefan. Sunny's suggestion worked immediately but the article by Henric is useful as always.

I am guessing that using the Len(Trim() solution may effect performance somewhere down the line?

[Anonymous]

Got it. So your expression could also be substituted with element function E(), that returns excluded values.

Very thankful again! Your help is always appreciated.

[swuehl]

Asking about performance, if OpportunityID is the primary key of your upper left table, I suspect that using set analysis will not perform much better than an ordinary aggregation with an if() clause,

I also think there can't be a case with OpportunityID linked to a LeadID value without also showing a ContactID, so it should be enough to check for ContactID being NULL.

=Sum(If( len(trim(ContactID))=0,Amount))