2 Replies Latest reply on Oct 29, 2013 11:15 AM by Ramon Martinez

# Histograms: creating bins based on mean and standard deviation

Hi,

I'm trying to create histogram bins based on the mean and standard deviation of the population.

I tried this by calculating 3 fields:
1. MEAN for the variable i'm trying to bin (in this case, score)

2. STDDEV for the variable i'm trying to bin (in this case, score)

3. BIN using the SCORE, MEAN and STDDEV using the formula below:

If [Score] < [MEAN]+[STDDEV]*-3 then 1
ElseIf [Score] < [MEAN]+[STDDEV]*-2 then 2
ElseIf [Score] < [MEAN]+[STDDEV]*-1 then 3
ElseIf [Score] < [MEAN]+[STDDEV]*0 then 4
ElseIf [Score] < [MEAN]+[STDDEV]*1 then 5
ElseIf [Score] < [MEAN]+[STDDEV]*2 then 6
Else 7
End

But it gives the following error:

"Cannot mix aggregate and non-aggregate arguments to function"

Does anyone have any suggestions on how to avoid this? I also tried using the fields MEAN and STDDEV in the parameters but this only gives a slider and I want only one value for this population

Thanks!

Flo

• ###### 1. Re: Histograms: creating bins based on mean and standard deviation

Hi Flo,

Sorry for the delay in receiving a response! It's hard to tell what the issue is without seeing the workbook. However, the error mentioned is addressed in this KB article:

Resolving "Cannot mix aggregate and non-aggregate arguments" Calculation Error | Tableau Software

-Tracy

• ###### 2. Re: Histograms: creating bins based on mean and standard deviation

Hi Florencia,

Try this formula for the Calculated field: Bin of Score

If SUM([Score]) < [MEAN]+[STDDEV]*-3 then 1

ElseIf SUM([Score]) < [MEAN]+[STDDEV]*-2 then 2

ElseIf SUM([Score]) < [MEAN]+[STDDEV]*-1 then 3

ElseIf SUM([Score]) < [MEAN]+[STDDEV]*0 then 4

ElseIf SUM([Score]) < [MEAN]+[STDDEV]*1 then 5

ElseIf SUM([Score]) < [MEAN]+[STDDEV]*2 then 6

Else 7

End

Define Bin of Score as Discrete

Drag Bin of Score to Column shelf and Number of Records to Row Shelf

Add the dimension that define the granularity of your data to Detail

Ad finally, right click on the pill Bin of Score, that you located on Column and select Compute Using > the dimension you added to Detail.

Try this an let me know your results. I would be easy if you share a package workbook with sample data.

Best,

Ramon