4 Replies Latest reply on Feb 27, 2018 10:44 AM by Yuriy Fal

# Using Averages to calculate confidence intervals and reference lines

I'm new to Tableau, so I'm not sure if I am tackling this right.Sample data:

YRMO     MemID     Num     Denom

201501     123          0               1

201502     123          0               1

201503     123          1               1

201504     123          1               1

...

201701     123          0               1

201702     123          1               1

...

Basically, I have data that ranges from 2015 to 2017 in a YYYYMM format (that I also broke out into Year and Month) for a list of members that would be used to calculate a rate per month: sum(num)/sum(denom). What I'm trying to do is create a "reference line" with a confidence interval that will display the average rate over the 3 years per month. So it can't be constant, which is the problem I've been running into. If in January the rate averages to be 55, I want the line to be at the 55 mark. But if the June average rate is 63, I want it to be at the 63 mark and so on. This is what I currently have looks like, but the reference line is just displaying the overall average not the average per month like I want it to. The point of the confidence interval is to help portray when the current month's rate is slightly lower than prior year but still within average/confidence interval. Any help would be greatly appreciated. • ###### 1. Re: Using Averages to calculate confidence intervals and reference lines

I have figured a way using my own calculated confidence intervals to use stacked area to shade the wanted band. I'd still like to know if there is a way to use Tableau's calculations? Again, I wouldn't want the reference line to be a whole average, but average of January 2015-2017 rates, then average of February 15-17 rates, etc.

• ###### 2. Re: Using Averages to calculate confidence intervals and reference lines

Hi Erin,

If you make the Month pill on Columns a Discrete (Blue)

and choose to calculate Ref Lines per Cell, you may get it.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 3. Re: Using Averages to calculate confidence intervals and reference lines

Thank you. This solved that question. However, it looks like it's better if I calculate my own confidence intervals display wise. I can smooth out the shading better.

• ###### 4. Re: Using Averages to calculate confidence intervals and reference lines

As you wish :-)