2 Replies Latest reply on Jan 22, 2016 1:44 AM by Winson Lui

    Showing lines of average in different year on the same chart

    Winson Lui

      Hi,

       

      After displaying the score for all the activities, grouped by region A, B, C and country A1,A2 ...., I would like to show 2 average lines on the graph, one would be average score for 2014 and another would be average score for 2015.

      However, by using the average button in the analytic tab, only the average for all data is shown in one line.

      Is there any way we can show 2 lines for 2014 and 2015 separately on the chart?

      Thanks.

       

       

      This is what I try to get.

        • 1. Re: Showing lines of average in different year on the same chart
          Esther Aller

          Hi Winson,

           

          In order to create lines that show the average data for just 2014 or just 2015, we must first create calculated fields that isolate the data for those years:

           

          1. Select Analysis > Create Calculated Field
          2. In the Calculated Field dialog box that opens, do the following, and then click OK:
            1. Name the calculated field. In this example, the calculated field is named "2014 Score"
            2. In the formula field, create a calculation similar to the following:

              IF YEAR( [Date] ) = 2014
              THEN [Score]
              END

            3. Select Analysis > Create Calculated Field
            4. In the Calculated Field dialog box that opens, do the following, and then click OK:
              1. Name the calculated field. In this example, the calculated field is named "2015 Score"
              2. In the formula field, create a calculation similar to the following:

                IF YEAR( [Date] ) = 2015
                THEN [Score]
                END
            5. Drag [2014 Score] to Detail on the marks card. This step is necessary because Tableau Desktop can only create reference lines from data in the view.
            6. Right-click [2014 Score] on Detail and select Measure > Average
            7. Add a reference line to the view
            8. In the Edit Reference Line, Band, or Box dialog, do the following and click OK:
              1. For Scope, select Entire Table
              2. For Line, select AVG(2014 Score) from the dropdown menu
            9. Repeat steps 5-8 for [2015 Score]

           

          Check out Understanding Logical Calculations for more information about how to use IF THEN statements to create calculations.

           

          I hope this answers your question!

          1 of 1 people found this helpful
          • 2. Re: Showing lines of average in different year on the same chart
            Winson Lui

            WOW. This is very helpful.

            Thank you very much.