15 Replies Latest reply: Sep 8, 2016 2:11 PM by Stefan Wühl

Sum Where Field Is Null in Data Model

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.

• Re: Sum Where Field Is Null in Data Model

May be this:

Sum({<[Contact ID] = {"=Len(Trim([Contact ID])) > 0"}, [Lead ID] = {"=Len(Trim([Lead ID])) = 0"}>}[Amount USD])

• Re: Sum Where Field Is Null in Data Model

thank you for the replay Sunny but this did not work. Not data is returned with this expression.

I can confirm there are records where CONTACT ID is NOT NULL and LEAD ID IS NULL

• Re: Sum Where Field Is Null in Data Model

Sum({<[Contact ID] = {"=Len(Trim([Contact ID])) > 0 and Len(Trim([Lead ID])) = 0"}>}[Amount USD])

• Re: Sum Where Field Is Null in Data Model

Brilliant. Thank you.

• Re: Sum Where Field Is Null in Data Model

Sunny - Does the syntax change if I want both fields to be blank? Trying to apply by changing > to = but it is not working

• Re: Sum Where Field Is Null in Data Model

This is not working?

Sum({<[Contact ID] = {"=Len(Trim([Contact ID])) = 0 and Len(Trim([Lead ID])) = 0"}>}[Amount USD])

You wanted rows where both Contact ID and Lead ID is null? Is that possible?

• Re: Sum Where Field Is Null in Data Model

That is not working. I've tried several variations

Below is proof that these rows exist:

• Re: Sum Where Field Is Null in Data Model

Sum({<[Opportunity ID] = {"=Len(Trim([Contact ID])) = 0 and Len(Trim([Lead ID])) = 0"}>}[Amount USD])

• Re: Sum Where Field Is Null in Data Model

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

• Re: Sum Where Field Is Null in Data Model

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.

• Re: Sum Where Field Is Null in Data Model

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.

• Re: Sum Where Field Is Null in Data Model

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.

• Re: Sum Where Field Is Null in Data Model

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?

• Re: Sum Where Field Is Null in Data Model

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

• Re: Sum Where Field Is Null in Data Model

another idea:

--> Lead ID null(), Contact ID not null()

=sum({(\$-<[Lead ID]={"*"} >) * (<[Contact ID]={"*"} >)} [Amount USD])

--> both fields null():

=sum({(\$-<[Lead ID]={"*"} >) * (\$-<[Contact ID]={"*"} >)} [Amount USD])

--> both fields not null():

=sum({(<[Lead ID]={"*"} >) * (<[Contact ID]={"*"} >)} [Amount USD])