13 Replies Latest reply on Nov 3, 2013 9:23 AM by Jonathan Drummey

# COUNTD at a different aggregation? [File Attached.]

Jonathan, I reworked the workbook with your suggestions, and applied them to both the table and the new map. All is functioning as expected. Now curtis.johnson wants to COUNTD the Customer IDs by country. [COUNTD because in his data there may be duplicate Customer IDs in a country.] Here's the table view, red numbers indicate the desired result:

What he really wants is these figures on his Map:

Any help appreciated by both of us.

--Shawn

• ###### 1. Re: COUNTD at a different aggregation? [File Attached.]

I played with this I promise. Got nowhere fast. Will think again.

1 of 1 people found this helpful
• ###### 2. Re: COUNTD at a different aggregation? [File Attached.]

Ahhh, thank you Alex! I beat my head against this wall thinking it "should be easy". Good to know I'm not missing something obvious. Thank you my friend.

--Shawn

• ###### 3. Re: COUNTD at a different aggregation? [File Attached.]

Here's a solution.

Ordinarily we'd go with as something like a SIZE() or TOTAL(COUNTD(Customer ID)) with an advanced Compute Using on the Customer ID, Skills, with At the Level of Customer ID. However, there's a table calc filter in play and the SIZE() or TOTAL() would be computed before the table calc filter and therefore be incorrect. This points at a way to frame the problem in terms of "How do I have a table calculation that is computed after a table calculation filter?"

A solution is to build the filter calculations into a computation that effectively gets us the count distinct. I set up the following # of Customers calc:

WINDOW_SUM(IF [Employee has all skills?] AND [First Skill in Customer] THEN 1 END)

This nested table calc has the following Compute Usings:

SIZE() = on Skills (this is the same as the advanced on Country, Customer ID, Skills, restart every Customer ID)

TOTAL COUNTD SKILLS - advanced on Country, Customer ID, Skills

First Skill in Customer (a FIRST()==0 calc)  - on Skills

# of Customers = on Customer ID, Skills

What it does is: if the customer is one that passes the filter *and* we're looking at the first skill for that customer, then return 1. Then the WINDOW_SUM sums those up within the partition of the Country to effectively give us that count distinct. Note that when you bring this into a view that has the Employee has all skills? calc, it makes the view empty. I'm not sure why Tableau does this, there's an interaction across separate table calcs that have underlying nested table calcs that aren't in the view that I haven't figured out yet. In any case, you can go in and manually set all the Compute Usings and everything will work.

Jonathan

• ###### 4. Re: COUNTD at a different aggregation? [File Attached.]

Hi Shawn,

For your peace of mind, I was also playing with this but without success so far.

Best,

Ramon

1 of 1 people found this helpful
• ###### 5. Re: COUNTD at a different aggregation? [File Attached.]

I can't wait to see the solution from Jonathan. Definitively, a to improve my skills, learning from a top notch Tableau Zen Master.

Ramon

1 of 1 people found this helpful
• ###### 6. Re: COUNTD at a different aggregation? [File Attached.]

*blushes* Thanks, Ramon!

• ###### 7. Re: COUNTD at a different aggregation? [File Attached.]

That's what I was missing - had tried various size, counting of index 1, and so on, but the number was always too high. I didn't realize the table calc filter would be the issue.

1 of 1 people found this helpful
• ###### 8. Re: COUNTD at a different aggregation? [File Attached.]

A thing to behold! Table calcs are singing. Thanks Jonathan.

--Shawn

• ###### 9. Re: COUNTD at a different aggregation? [File Attached.]

We really need a way to add threads like this to an area of the forum.  "Amazing Solutions", or something like that.  Even a personal bookmarks area would be better than me trying to keep track of stuff like this on my own!

1 of 1 people found this helpful
• ###### 10. Re: COUNTD at a different aggregation? [File Attached.]

That's sort of what TabWiki is suppose to be. I'm sure if you started one for this, Jonathan would be happy to edit it. Also there is the TCRL area for calculations, and this definitely belongs there. Write it up, and others will edit.

--Shawn

• ###### 11. Re: COUNTD at a different aggregation? [File Attached.]

Thank You everyone for so much help!!!!!

I will work at incorporating Jonathan's magic into my actual workbook this morning and will let y'all know how it goes.

Again, I cannot thank everyone enough for your support in helping to solve this challenging problem.

Curtis

• ###### 12. Re: COUNTD at a different aggregation? [File Attached.]

Thank you Jonathan & Shawn for all of your superb work and support.  Wonderful experience to learn from the best!!!

Sincerely,

Curtis

• ###### 13. Re: COUNTD at a different aggregation? [File Attached.]

You're welcome, Curtis!

Shawn, I'll write something up on this, and I can't promise any timeframe for getting it done.