Thank you for your quick reply! You've taken me part way to a solution. Here's what I found.
1. I can get the calculated field for Qty Most Recent Year to work. And by matching on YEAR([As Of Dt]) - which is a field in every row of my joined data - I'm able to make it dynamic as new years are added. Great!
I know it works, because when I temporarily add it to the viz, I see that it has the correct values:
2. The RANK_DENSE() function, not so good. It keeps calculating rank horizontally, as rank within the 3 years, not vertically. Indeed, it even tells me that it's calculating "across".
I went into Table Calculations and set it to compute using Table (down), but the result is nowhere near what I'm after.
At this point, i'm just whacking at things uselessly, the Sorcerer's Apprentice. I need to understand more deeply. I think it has to do with how to use RANK() when there are more than one column, but I can find no examples of this on the net.
if you could attach sample mocked up data as twbx, the support becomes much much easier?
Of course. Here's the twbx downloaded from Tableau Public. The sheet I'm working in is called "Leader Board (2)". Let me explain the data. It consists of:
- Our Aircraft. 4,678 rows, one row for every individual aircraft in the U.S. Federal Aviation Administration's current aircraft registry, of a make and model that interests me. The key field is [N-Number], the number assigned to each aircraft.
- Inventory By Year. 33,578 rows, one row for every individual aircraft + a year in which it existed, through 2018. Thus, if an aircraft was manufactured in 2016, it will have 3 rows in this table - 2016, 2017, and 2018. Thus, by summing up the joined tables where [Year] = say, 2017, I can get the total number of aircraft in the fleet in 2017. Counting for 2018 yields the total number in 2018. The difference from 2017 to 2018 yields the number added to the fleet in 2018. These figures are the substance of the "Leader Board" sheet.
But that's all background. The heart of the problem is how to get the RANK() functions to work on just one of the [Year] columns when more than one Year column is showing in the viz. In my case, I want rank in the most recent year, which is currently 2018.
I can get the result I want if I restrict the viz to just 2018. (Or, rather, since Tableau needs two years to calculate a difference, I filter to 2017 and 2018 and then hide the 2017 column.) I then set the table calculation to Down. The result:
This is with:
But, again, this would be more useful if it showed three years. You could then see how the top makes & models are jockeying for market-share position over time. That's where I'm trying to go.
Shin, I REALLY appreciate your willingness to mess with this.
Expand Rank across year
If this works, please mark my answer as correct / helped, not from inbox but from original post.
YES! IT WORKS! Thank you, thank you! I appreciate the time you put into this and the clarity of your answers. Pictures and the twbx file help so much. For the record, in case anyone else comes this way, here's a summary of the solution you gave me in your twbx. It's a little simpler than your last post suggests.
1. Create these calculated fields that build on each other. Change the names to suit yourself. All are invisible except the last one, "Rank", which appears as a column head in the table.
2. Make Rank into a discrete field.
3. Drag it to the Row shelf as shown here:
4. Set the table calculations on the Rank pill where it sits on the Row shelf. Do as Shin said:
Also, using the drop-down pick list opposite "Rank" in the above panel, set:
5. Set the Make & Model sort to alphabetical within ranks. It apparently does not sort automatically.
As you see from the screen shot above, Shin gave me just what I wanted.
1 of 1 people found this helpful
Shin's replies opened the door and got the result that I needed. He's tenacious, brilliant, and clear. Case closed with many thanks.