5 Replies Latest reply on Aug 15, 2018 2:59 AM by Juko Vähätiitto

    Calculating and overlaying aggregates of several lines

    Juko Vähätiitto

      Hi,

       

      I wish to combine individual lines and an aggregation of them into same graph. In short, I wish to compare several years of temperature data to different benchmarks. I have no idea how to do some aggregations though, so I would be really grateful if someone could help me a little.

       

      At the moment, I have 16 years of hourly temperature data. I have made duration curves of them and intend to view them one month at a time, so for January I will have 16 curves. Now I wish to add an aggregate to the same graph. I succeeded in overlaying another duration curve on top of my graph, as shown in the example workbook worksheet "Pysyvyyskäyrät" (the other tabs are not relevant). "Lämpötila" means temperature. Each January has 744 hours, so the "all Januaries" data set has 16 * 744 = 11904 data points. The aggregated data shown is a duration curve of this larger dataset. I did this by duplicating the data source, blended it to the original and combined the graphs using dual axis. What I want to do in addition to this, is to calculate an average (or median, or percentile) of the 16 curves, so for 744 different points, average of 16 different rank 1 hours, rank 2 hours etc all the way to 744th point. That would give me sort of middle line of the ensemble. How could I do this and show it in the same graph as the individual lines, like I did in the example with another aggregate line?

       

      I'm using Tableau desktop 2018.1.1.

       

      - Juko

        • 1. Re: Calculating and overlaying aggregates of several lines
          Patrick A Van Der Hyde

          Hello Juko,

           

          Are you trying to capture the the Median per percentile or the median across all the values?  You can use reference lines for the former.  The second is going to need another table calculation or a level of detail calculation however getting it on the viz will be challenging with the current design. 

           

          Patrick 

          • 2. Re: Calculating and overlaying aggregates of several lines
            Juko Vähätiitto

            Hi,

             

            Thank you for your answer

             

            It's easy to do what I want in Excel, and the example probably clarifies the issue somewhat. The Excel is attached to this post. The excel graph is also attached here. So basically what I want to draw is the average of the ensemble, if that explanation makes any sense. I want the "reference curve" to describe the individual curves, not the dataset.

             

            - Juko

            • 3. Re: Calculating and overlaying aggregates of several lines
              Jonathan Drummey

              Hi,

               

              Is this something like what you want?

               

              Untitled.png

               

              For the data source I used the Data Interpreter and then Tableau's  Pivot feature to create a source that has one row per F1/ID and year. The Year is the Pivot Field Names field, and the values now in Pivot Field Values.

               

              In the view the first AVG(Pivot Field Values) pill has Pivot Field Names on Color so we get a line for each color. Then the second AVG(Pivot Field Names) pill creates a second marks card and on that there are no dimensions on color so we get one line that is the average across all years for each F1. This gets the -20.6485 result that you're expecting for the value.

               

              For more information about dimensions & measures and how Tableau lays out views check out Understanding Pill Types, and the other "Why is Tableau doing that?" videos in that section on of the online training.

               

              v10.5 packaged workbook is attached, hope this helps!

               

              Jonathan

              1 of 1 people found this helpful
              • 4. Re: Calculating and overlaying aggregates of several lines
                Juko Vähätiitto

                Thank you! This is exactly what I wanted. I didn't know how to use the Pivot feature. Simple and easy to do in excel, but I simply didn't know how to do it in Tableau.

                 

                - Juko

                • 5. Re: Calculating and overlaying aggregates of several lines
                  Juko Vähätiitto

                  On closer look, that actually didn't solve my problem, because you were using my excel as a data source, where manual organizing of data had already been done. That however led me into right direction.

                   

                  I managed finally to figure out a solution, although not very elegant one. I calculated the rank of each hour in the way I wanted, that is, ascending rank by temperature, starting over each month and added that to the data source. In that way, the correct ranking remained independent of my view. I could not find a way to calculate view independent rank in Tableau. After that it was quite easy, I just replaced the view dependent x-axis ranking with the fixed ranking, after which the reference graph could average all rank 1 points, rank 2 points etc.

                   

                  If someone knows how to calculate view independent ranking in Tableau, that would be nice to know. Now I made an Excel calculation, where I sorted the data

                   

                  1. By year

                  2. By month

                  3. By temperature, ascending

                   

                  Then added a column where if month changes, value is 1, otherwise its previous +1. Now this column ranks each months hours from coldest to hottest. Now I can also take each January, which all have 744 ranked points, and draw graphs as I wish.

                   

                  - Juko