
1. Re: Calculating and overlaying aggregates of several lines
Patrick Van Der Hyde Aug 9, 2018 3:26 PM (in response to Juko Vähätiitto)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 Aug 9, 2018 11:23 PM (in response to Patrick Van Der Hyde)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

Duration curves.xlsx 262.5 KB


3. Re: Calculating and overlaying aggregates of several lines
Jonathan Drummey Aug 12, 2018 10:54 AM (in response to Juko Vähätiitto)Hi,
Is this something like what you want?
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

pivot to average.twbx 438.3 KB


4. Re: Calculating and overlaying aggregates of several lines
Juko Vähätiitto Aug 13, 2018 12:23 AM (in response to Jonathan Drummey)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 Aug 15, 2018 2:59 AM (in response to Jonathan Drummey)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 xaxis 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