Not counting nulls in unique count

I want to create a formula that counts all of the unique values except for the null.  Is there a good way to do this?

10replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hello Bill.

    Essentially what you can do is to do a unique count and then subtract 1 if there was a null value in it. So something like this

     

    unique count ( user_id) - max( if (isnull( user_id)) then 1 else 0)
     
    Reply Like 3
  • Siva Singaram  Bill Back

    Thanks. This worked. However , I'm running into an issue when I drill down. It shows more records than the summary is showing. Any ideas?

    Reply Like
  • Ayo Olatunji 

    When you drill down on a particular attribute, the totals of the unique count can be greater than the summary row because many value of the attribute could have the same value of the field on which you are doing the unique count but each row of the drill down should not be greater than the summary.

    Let me show through an example. Assume the data is as follows

    Product Id               Region

    101                             East

    102                             East

    101                             West

    103                             West

    103                             Central

     

    If we do a unique count of the Product ID, it will show 3. But if we drill down by region the answer would be like

    Region       Unique Count

    East                 2

    West                2

    Central            1

     

    In this case, the sum of the Unique Count is 5 even though the Unique Count before drilling down was 3. Let me know if this answers the question.

    Reply Like
  • Thanks Siva for the clarification. So what i am seeing is a little different.

    Im using a formula that looks like this :

     

    unique count ( if ( change type = "new") then accountkey else null )  - max( if ( change type != "new") then 1 else 0)

    Which gives me the count I want .

    but when I drill down, i see extra records that do not have a "Change type" of "New" . Im expecting to only see "New".

    The extra records show a "0" which means that they don't have a change type of "new". But they are still included in the drill down.

    Not sure if the explanation helps

    Thanks

    Reply Like
  • Ayo, the underlying records will show the data that was used to create the answer.  The formula give you a value based on those records, so it's showing all the records.  

    Reply Like
  • Ayo Olatunji 

    Got it now. If you do not wish to see the other records, the only thing I can think of is to put a filter on this formula field, to show only if the value is greater than 0

    Reply Like
  • That worked. Thanks guys. Will follow up with Bill during our next meeting. 

    Reply Like
  • Wanted to chip in on this thread. I think the unique count should omit null values similar to the behavior with SQL Count/Count Distinct where only non-null values are counted. 

    Reply Like
  • I was able to achieve counting nulls by doing the following formula: 

    unique count (account) - unique count (isnull(account))

    Reply Like 1
  • Siva Singaram You need to explain this formula :-)

    Reply Like
Like1 Follow
  • Status Answered
  • 1 Likes
  • 3 wk agoLast active
  • 10Replies
  • 935Views
  • 5 Following