8 Replies Latest reply on Aug 29, 2016 7:14 AM by Jaspreet Narang

    Trend Lines in Dashboard

    Jaspreet Narang

      Hi Everyone,

       

      I am trying to show a trend line in my dashboard but it seems like the average value being displayed is not correct (Version 9.0).

       

      The average value for 8/15 is 7.4 and for 8/22 is 12.1 in my data source but the values displayed on the dashboard is different

       

       

      The data source that I am using to create this has 3 tabs. The first tab is joined to the second using Project ID as the Primary key and joined to the third using Sponsor as Primary key.

       

      Can someone please help me with this ?

       

      I have attached the workbook and the data sources also.

       

      Thanks,

       

      Jesse

        • 1. Re: Trend Lines in Dashboard
          chris.moore.11

          The problem is the join returns duplicates for the "team members" field you're taking an average of. Go to analysis -> view data -> underlying. Then look at the field you are taking an average of and you will see all the duplicates.

           

          I've thought about this problem briefly before and couldn't come up with a simple solution within the workbook, but i would love to hear one if anyone has one.

           

          You could use custom SQL to create a field that populates nulls for all but one for each team, or just using a separate data source without duplicates for that specific sheet/chart may be easiest.

          1 of 1 people found this helpful
          • 2. Re: Trend Lines in Dashboard
            Jaspreet Narang

            Hi Chris,

             

            Thank you for the response. I will try doing it with a separate data source and let you know of the results.

             

            Best Regards,

             

            Jesse

            • 3. Re: Trend Lines in Dashboard
              Naveen Agarwal

              Appears that your data shows sum of Team Members for Aug 15 is 615, count is 87; so the average = 7.1. The calculation is right based on underlying data.

              1 of 1 people found this helpful
              • 4. Re: Trend Lines in Dashboard
                Jaspreet Narang

                Hi Naveen,

                 

                Thank you for the response. I think the problem here is that I have 3 tabs and the join with the second one is causing the problem. For example a project has 4 team members and has 3 high issues so the total comes out to be 4 x 3 = 12 members instead.

                 

                Because for Aug 15 my count is only 140 and average is 7.4

                 

                 

                I can't seem to figure out a solution for this.

                • 5. Re: Trend Lines in Dashboard
                  Jaspreet Narang

                  Hi Chris,

                   

                  I tried doing it with a different data source with no duplicates but the dates are completely messed up. It says 12/30/1899 12:00 AM instead.

                   

                  • 6. Re: Trend Lines in Dashboard
                    chris.moore.11

                    This will be difficult for others to help with, as its an issue with the underlying data not really tableau.

                     

                    With the original workbook create a field using this:

                     

                    SUM({ INCLUDE [Project ID (Team Members)] : sum([Team Members (Team Members)]) / count([Project ID (Team Members)]) })/ COUNTD([Project ID (Team Members)])

                     

                    Then drag that to the row shelf.

                     

                    See the attached as an example.

                     

                     

                    That would work except for you have multiple values for one of your teams...

                     

                      

                    DateProject ID (Team Members)Team Members (Team Members)
                    8/15/2016Workplace110
                    8/15/2016Workplace15
                    8/22/2016Workplace15
                    8/22/2016Workplace115
                    8/22/2016Workplace120
                    1 of 1 people found this helpful
                    • 7. Re: Trend Lines in Dashboard
                      Jaspreet Narang

                      Hi Chris,

                       

                      I didn't even realize the project id was same for some projects. Thanks for pointing it out. I will try the calculated field on my view and let you know of the results.

                       

                      Best Regards,

                       

                      Jesse

                      • 8. Re: Trend Lines in Dashboard
                        Jaspreet Narang

                        Hi Chris,

                         

                        Thank you so much for your help. That worked like a charm.

                         

                        Best Regards,

                         

                        Jesse