11 Replies Latest reply on Jun 15, 2016 11:33 AM by savas.ziplies

# Building a unique Count Distinct over all values down a dimension (unique running count)

Hi

I face the following scenario:

I have multiple data sources, everything is filtered, I result in a set of leading values (keys). Each key has an amount of data in another data source I would like to count distinct. That works, as far as I can tell.

What I would like to have is a unique count, meaning the data in the other data source should only be counted once in the whole calculation.

So, for example I have a filtered key list of

* Key A

* Key B

* Key C

Each key has some part numbers in the other data source, and some of these might be overlapping. In total, I only want to have them counted once. So, if the same part number is on Key A and Key B, the total count should be 1.

How could this be achieved?

• ###### 1. Re: Filtered Data getting unique Count Distinct over all values

If you share a sample workbook then we can try some alternative way to get the desired output.

Mahfooj

• ###### 2. Re: Filtered Data getting unique Count Distinct over all values

Hi

Ok, I tried to make an example workbook (attached).

So, maybe in detail on the workbook given: There are multiple groups. Groups have a key. The key matches IDs. IDs can be overlapping. For a year (not in the example but that's why there are start and end dates) I would like to have all groups, their IDs counted, and the difference in unique IDs, not matched by another group, visible.

As you can see, I have the Groups and their COUNTD. I can add a Total and it is the unique count over all. The problem is, I have a total over all (=9), whereas it should be 8, because of the matching groups (the one different ID from key D470920 should not be counted). I cannot filter it, because I need the full data also for another calculation. So, I guess I have to make the total calculation myself resulting in 8, but don't really know how.

Additionally, what I am looking for now is basically a waterfall.

So, for example, I have 3rd, it has 5 IDs. Evo has three times the same ID and two different, so the count I would like to have is +2 and so on and so forth. So that in total from the running values I will end up with the total presented, meaning 8 unique IDs.

I hope the example sheds some light onto my problem.

Thanks a lot for any help

• ###### 3. Re: Filtered Data getting unique Count Distinct over all values

In tableau blending is nothing but left outer join in database. You've to filter the null value to get the correct record set with with count of ID.

And waterfall is not possible on this data. For waterfall chart you should have at least one measure. Though none of the data sources have a single measure.

workbook attached for your reference. Have a look and let me know If you've any query.

Mahfooj

• ###### 4. Re: Filtered Data getting unique Count Distinct over all values

Hey there

Thanks for that.

Ok, the filtering I totally get and was my bad I didn't clearly think about it.

But, so this is not possible at all? So, I cannot get a unique count over the values (even if not graphically, just the values)?

So, no way at all for

3rd: 5

Evo: 2 (or maybe even 7, if that would be easier)

...

How would I have to change the data to be able to get something like that?

Thanks a lot

• ###### 5. Re: Filtered Data getting unique Count Distinct over all values

Actually you're trying to intersect along groups. You can follow the below link for more details regarding intersection in tableau.Brainstorming Multi-Select Filters: INTERSECTION instead of UNION

You can try some other options. See the workbook.

• ###### 6. Re: Filtered Data getting unique Count Distinct over all values

Hi there

Thanks for the hint. I did some googling now and read your suggested link but cannot really see how this could help me, or better: how this could be applied to my case for the intersecting distinct counting.

If you or somebody else has any more information or can point me to something leading to

3rd: 5

Evo: 2 (or maybe even 7, if that would be easier)

...

I would be very happy and appreciate it.

I am really sorry I cannot really see the solution at the moment.

Thanks

• ###### 7. Re: Filtered Data getting unique Count Distinct over all values

Sir Jonathan need your. If you get some free time then please have a look in this issue.

• ###### 8. Re: Filtered Data getting unique Count Distinct over all values

Hi Savas, Tableau is doing exactly what you are telling it to to do. There are 9 values of ID in the data an that's what Tableau is counting. If you don't want a value of ID to be counted then that needs to be filtered out in some way, what is the other calculation you refer to? Is it one in the same worksheet or a different worksheet?

Hi Mahfooj, please please please do not describe data blending as "nothing but a left join", that's an oversimplification that can lead to problematic assumptions. See TDT: Data Blending - Left Join or not? - presented March 26, 2015 for more details.

Jonathan

• ###### 9. Re: Filtered Data getting unique Count Distinct over all values

Hi Jonathan

Thanks for coming back to me.

Yes, I of course got that I have to filter the Groups I don't want to. I got that and that was totally my inability yesterday to grasp a clear thought.

I attached the workbook again for you and made a screenshot of it. The scenario is: I have two data sources, one with the amount of data, one grouping these together.

In detail: There are multiple groups. Groups have a key. The key matches IDs. IDs can be overlapping. For a year (not in the example but that's why there are start and end dates and what I want to segregate in my full data) I would like to have all groups, their IDs counted, and the difference in unique IDs, not matched by another group, visible.

As you can see, I have the Groups and their COUNTD. I can add a Total and it is the unique count over all. The problem is, I have a total over all (=8). What I am looking for now is basically a unique running count.

So, for example, I have 3rd, it has 5 IDs. Evo has 3 times the same ID and 2 different, so the count I would like to have is 2 (or 7 if that is easier to achieve) and so on and so forth.

3rd: 5

Evo: 2 (7)

...

Grand Total: 8

So that in total from the running values I will end up with the total presented, meaning 8 unique IDs.

I hope that makes sense. What I am looking for is exactly that, what is currently being derived manually from the same data and we would like to automate it. But especially when it comes for the unique look at IDs really kills me atm.

Sorry for all the asking and incompetence atm ^^' Thanks a lot

• ###### 10. Re: Filtered Data getting unique Count Distinct over all values

Thanks for sharing this valuable information Sir! Learned new thing today. Keep on guiding us with your knowledge. Respect!

• ###### 11. Re: Filtered Data getting unique Count Distinct over all values

Also thanks from me Mahfooj for your help so far.

Hopefully Jonathan can finally solve my daily riddle ^^

Thanks a lot