Thanks for the response. Yes that is exactly what I was looking for however it seems the sample data I supplied was not enough to replicate the issue I was having. I've attached a new workbook and sample data. As you can see I replicated exactly what you did but I still don't get the desired results.
3 of 3 people found this helpful
I believe I've found a solution, as long as you have 8.1 installed. I am not sure a solution exists, using the INDEX calculation alone -- there is a reference to Dense Rank in the TCRL: Dense Rank
The attached uses the RANK_DENSE calculation on a TOTAL(Sum(Sold)) field so each stacked bar is evaluated based on the Total Sales across all Item Types in that Location. I realized I had to use RANK_DENSE, when the RANK calculation was returning results like 1, 4, 6, etc (based on the # of item types in each location). RANK_DENSE ensures we get consecutive values in each row. Very glad I got interested in this problem and didn't give up, as I now have a better understanding of the various RANK types in 8.1
The compute using on the Total Sold calc is: Addressing on Location, then Item Type, restarting each location. The compute using on the Rank calc is set to address on Location, then Item Type, with no restarting value.
I hope this helps--it took me over an hour to get to this solution, but I knew there had to be a way!
Jonathan Drummey - Is there anything that would make this easier, or anything you'd do differently here? Just curious, if you have the time to respond. I would've given up on this long ago if it wasn't for your inspirational work and constant help.
That did the trick. I can't believe how complicated the solution was for something that I thought was simple. I spent more time than I care to admit on this particular request. Regardless thank you for your help!
That is part of the fun (and/or "challenge") with using/learning Tableau - it is very satisfying to create a solution, but its just not always easy to learn and understand Tableau without help; it definitely takes practice, experience, and user feedback to learn this tool (and an understanding of some basic data concepts)--and I could do more to get feedback from more experienced users. The good news is that there are more resources than ever to help us with that.
You just ran into one of the archetypal uses for At the Level. With the Location Sorted by Sum(Sold)/Desc, we can use the INDEX() calculation with Compute Using of Location, Item Type, At the Level Item Type and get the desired results:
I know a couple weeks ago you'd asked for a deeper description of At the Level, I've got one mostly written up (probably next week before it comes out). The key idea here is that with At the Level, the Index() is incrementing on each location and returning the same result to every Item Type within that location.
Awesome, thanks, I really do appreciate this. I thought I had tried using At the Level, but must have never gotten it quite right. I am just struggling with how to predict/know in advance what the best approach is. It seems the only thing that works for me is practice and experience -- and asking others for help/advice!
after desperately looking for an answer to my problem or at least someone who shared it, I tried following your example and got much better results than doing it by myself. However, I have an additionall issue: my ranking includes up to 100 locations and I do want to improve the performance of a dashboard where this ranking is only one of several graphs and limit the default view to only the first 5 by volume. I tried following the steps with above example and additionally to include a quick filter but that does not really do the trick neither does creating sets help as they lead me to exactly the problem that was described initially by Ryan. Any ideas?
Thanks a lot for your support / help
You say things aren't helping and not doing the trick, however you haven't specified what the current performance is nor your goal for improvement. You also haven't specified volumes, what kind of data source, whether your hardware is adequately sized for your volumes, etc. And when you say you tried following the steps above and to include a quick filter, without more details we can't know whether what you implemented is what Matthew or I described, nor have you described your data in sufficient detail for us to understand the problem.
So we can't answer your question with what you provided. I have two suggestions:
1) Run the performance analyzer on your dashboard and get a sense of which worksheets are causing you performance issues. It might not be this one, but another one.
2) If you think this worksheet is still an issue, then I suggest posting a packaged workbook (.twbx) with some sample data and then we can take a look.