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

    Using Averages to calculate confidence intervals and reference lines

    Erin Slott

      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.