7 Replies Latest reply on May 17, 2014 6:19 AM by Noah Salvaterra

# How to solve the problem of a new aggregation based on the other aggregation

Hi folks,

I have a problem. First, I have a calculated field named "Average gain" which is "AVG([Posttest answer])-AVG([Pretest answer])". Then I want to know the standard deviation of this "Average gain", but "STDEV([Average gain] )" is wrong as Tableau complains "STDEV (an aggregate function) is already an aggregation, and cannot be further aggregated." How can I solve this problem? I need this SD!!!

Qin

• ###### 1. Re: How to solve the problem of a new aggregation based on the other aggregation

Ah, but what standard deviation do you need? Tableau is absolutely correct to be confused in this situation. If you drag just your average gain calculation into the view, it is a single number. Standard deviation isn't typically defined for a single number. If you have a dimension in your view, say State or School, it will divide your data into some number of buckets but within each bucket there is just one bucket, so within each bucket, no standard deviation. Beyond the intricacies of standard deviation, there is a Tableau issue in play as well Tableau doesn't want to let you aggregate an aggregate because it can't see any earthly reason why you'd want to. It has already boiled things down to a single number for each bucket in your view. What you need is some way to aggregate across all (or at least some) of your already aggregated buckets. In Tableau, such calculations are known as Table calculations. In this case, the one you may find useful is Window_StDev([Average Gain]). This may work right out of the gate (let me know), or it may require a bit more setup. To help with that I'd need more information about how your viz is setup, a screenshot might do the trick (be sure to include the arrangement of your fields in Tableau, since I'll look at this with much more interest than the actual picture in the middle).

N.

1 of 1 people found this helpful
• ###### 2. Re: How to solve the problem of a new aggregation based on the other aggregation

Hi Noah,

Your response is very helpful. I think I get your point that it's meaningless to calculate the SD of a single number (average gain). What I want is to get the gain of each student first, and then calculate the SD of these gains. I make a sample which is downloaded free.

Profile

Thanks,

Qin

• ###### 3. Re: How to solve the problem of a new aggregation based on the other aggregation

Thanks for the free workbook, I hate when I have to pay for them!

I've attached an updated version that I believe should work. Let me know if this does the trick or if you require some additional explanation on this.

N.

• ###### 4. Re: How to solve the problem of a new aggregation based on the other aggregation

Hi Noah,

These formulas are exactly what I want, awesome!! It takes me a lot of time to understand the logic behind them, and I think I get it when I achieved the plots with the original education research data. I do like your error bar style which has two small "ears" showing the start and end of the error. I almost made the same shelves with yours, but I failed. So could you share your experience how to make that?

Many thanks,

Qin

• ###### 5. Re: How to solve the problem of a new aggregation based on the other aggregation

How to make which? The "ears" are reference lines, is that what you mean?

1 of 1 people found this helpful
• ###### 6. Re: How to solve the problem of a new aggregation based on the other aggregation

Oh, yes!!!

These "ears"-reference lines showed in my plot!

Thank you, Noah. You are really really great!

• ###### 7. Re: How to solve the problem of a new aggregation based on the other aggregation

Glad I could help, it is no problem. The sample workbook helps a lot, in general you'll find people answer very quickly when you include a workbook.

There were some tricks for using reference lines to build box and whisker plots prior to their introduction in 8.1. I thought your example looked a bit like a box and whisker already, so that is what I was thinking of with the ears.

N.