11 Replies Latest reply on Feb 15, 2012 12:11 PM by Alex Kerin

# Count Distinct Aggregation

Hello,

It's going to be difficult to describe what I'm trying to accomplish, so I'm going to attach a packaged workbook and base my description off of that.  In the workbook, I have a category ID, and a sub-category id.  In the tab "Count Distinct," I have counted how many distinct Sub-Category IDs there are for each Category ID.  You can see that there are 4 Category IDs (3,4,5 and 6) that contain 1 Sub-category ID, 2 (1 and 7) that contain 3 Sub-Category IDs, and 1 (2) that has 6 Category IDs.  I want to show a view that counts the number of Category IDs that have a certain count of sub-category IDs.  So I would want it to be something like this (based on numbers in packaged workbook):

Number of Sub-Categories within Category, Count

1, 4

3, 2

6, 1

Is this possible?  I'm assuming it would have to be a table calculation of some kind, but I can't figure out what it would look like.  This issue has come up multiple times so I figure it's worth asking about.

Thanks in advance for any help.

• ###### 1. Re: Count Distinct Aggregation

This is really interesting and I can't get my head around it - we are trying to use the distinct count of subcategory IDs as a partition for the window calc. I can get your answer, but only by stacking marks in a bar chart - see pic. Can't think of how to do this otherwise.

• ###### 2. Re: Count Distinct Aggregation

Ha, okay, the table calc is simply window_sum(countd([Cat IDs])), but the secret sauce is that we have to be able to use the countd of subcategories in the table calc - we do this by right clicking on the pill and deselecting - see pic. We then compute using this.

We can now partition based upon this - see tbwx

• ###### 3. Re: Count Distinct Aggregation

Hey Alex, thanks for the help.  I looked at the attached packaged workbook, and it looks like all of the numbers are overlapping.  How can I separate them out?

• ###### 4. Re: Count Distinct Aggregation

Wrong book, sorry:

• ###### 5. Re: Count Distinct Aggregation

Here's a patch for the more general case of "Cannot aggregate an aggregate..."

I -- and no doubt, others -- have a quick and dirty hack for when we need to Aggregate a COUNTD Calculated Field (or other calculation that is considered Aggregate):

Just either add zero, or multiply by 1.  The resulting Calculated Field is not considered an Aggregate, and thence, it is possible to Aggregate it in your viz.  It doesn't hurt to Optimize the Extract if dealing with a lot of records with this kind of calculation in it.

• ###### 6. Re: Count Distinct Aggregation

Nice Ken, thanks for this - I was unaware of this

• ###### 7. Re: Count Distinct Aggregation

Ken I agree with Alex, very nice tip. But I'm also thinking your Bio is missing a bit of specificity... Tableau, right?

--Shawn

• ###### 8. Re: Count Distinct Aggregation

Hi Shawn,

No I'm just a customer.

• ###### 9. Re: Count Distinct Aggregation

Then I'm truly impressed! Glad you jumped in. (And I like the new avatar that just popped up!) Civil War, that's unusual.

--Shawn

Message was edited by: Shawn Wallwork

• ###### 10. Re: Count Distinct Aggregation

Thanks Alex (and Ken and Shawn),

Looks like that works.  It was pretty slow for my large dataset but I'm guessing if I optimize the extract that issue will go away.  Really appreciate the help.

• ###### 11. Re: Count Distinct Aggregation

You may want to try this instead as the table calculation:

IF FIRST()==0 THEN

window_sum(countd([Category ID]),0,IIF(FIRST()==0,LAST(),0))

END

This limits the number of times the calculation is done - I would be interested if it speeds it up