8 Replies Latest reply on Oct 14, 2013 9:41 AM by Emily Le Coz

# Agg and Non-Agg Fields - Help!

I know this has been asked before, and I apologize for covering the same ground, but I have tried the solutions posted in previous threads with no luck.

I have two fields: SUM(Number of Records) and AGG(Unique Provider Numbers). I want a table calculation that returns SUM(Number of Records) / AGG(Unique Provider Numbers).

I get one of two errors depending on my possible workaround. It either says I can't mix agg and non-agg fields. Or that I can't further aggregate and agg field. I've tried everything. Help?

• ###### 1. Re: Agg and Non-Agg Fields - Help!

Have you tried SUM(Number of Records)/(Unique Provider Numbers)

If Unique Provider Numbers is already aggregated, no need to wrap it in another aggregation.

• ###### 2. Re: Agg and Non-Agg Fields - Help!

Thanks. I did try that one, and it gave me this error: "The formula must be a valid table calculation."

I'm stumped.

• ###### 3. Re: Agg and Non-Agg Fields - Help!

So I assume that "Unique Provider Numbers" is a table calculation?  Can you post the workbook, or a sample workbook with the same kind of setup?  If not, let us know what that calculation for Unique Provider Numbers is.

• ###### 4. Re: Agg and Non-Agg Fields - Help!

"Unique Provider Numbers" is a calculated field I created to return me the unique values of another field called "Provider Numbers." The calculation was just this: COUNTD([provnum] )

Basically I have three fields for my text table viz: "States", "Number of Records" and my calculated field called "Unique Provnum".

States = U.S. states

Number of records = Number of violations

Provider numbers = Nursing homes

Unique provum = Nursing homes (avoiding duplicates that appear since some nursing homes have multiple violations)

I dragged States, Records and Unique into the viz and tried to do a customized table calc. That's where I'm stuck.

How do I post a workbook? I'm still new to this.

• ###### 5. Re: Agg and Non-Agg Fields - Help!

OK.  I don't believe you need this to be a Table Calculation.  Try just creating this as a regular calculated field.

To attach a workbook, go to "Use Advanced Editor" in the top right corner of your message box--then, you'll see "Attach" at the bottom right hand side of this window.

• ###### 6. Re: Agg and Non-Agg Fields - Help!

OMG, you're a genius! I just created a calculated field and it worked beautifully!

How do you know when to do a calculated field and when to do a table calculation? What's the difference?

• ###### 7. Re: Agg and Non-Agg Fields - Help!

I'm really not, but thanks anyway .  I've only been doing this myself for 5 months or so, but there's a whole lot of material to learn.  Per the link below, Tableau defines a Table Calculation as (I don't have my own definition, but typically I use a Table Calculation to calculate something I cannot get any other way!)

Table calculations are computations that are applied to the values in the entire table and are often dependent on the table structure itself.

Table Calculations | Tableau Software

Some basic examples of Table Calcuations include: Percent of total, ranking calcs, moving averages, year over year comparisons, etc.

Some good info on learning Table Calcs here: Want to Learn Table Calculations? Here’s How! | Drawing with Numbers

• ###### 8. Re: Agg and Non-Agg Fields - Help!

Thanks so much. (You're actually really good at this considering you've only been doing it five months.)