I have bar chart on which i am using a Top X slider parameter to show only the Top 5 or so bars. This all seems to be working fine with nothing on the color shelf, however, when I drag my 'Data' domension to the color shelf, an extra 2 or 3 countries show (i.e. it now shows the top 8).
All the countries I have in my data extract have the same data types (i.e. Orthopaedic & implants, Electrodiagnostic apparatus etc), with the exception of 3 countries, namely, UK, France and Russia. These 3 countries have extra data types (Therapeutic Appliances, Portable Aids, and Imaging parts & accessories)..............
It so happens that these 3 countries are always the extra countries that appear when I attempt to color by Data, presumably because of these extra datatypes.
Any ideas what I'm doing wrong and how to get the desired result.
Thanks in advance
top_x_problems.twbx 163.0 KB
You were showing the top N countries for each Data value independently. So as different countries had different combinations of Data values, you ended up with more than N countries showing.
The trick (as always with table calcs) is in the definition of the partitioning.
Here's what I had to do.
Firstly I had to put a copy of your calculation on the Level of Detail shelf as well as on the filter shelf (I don't even know how you managed to get it on to the filter shelf without being on LoD).
I then adjusted the partitioning using "Advanced" and "At the level". I confess to using the well-known table calculation technique of "trial and error until you get the answer you were looking for", so I won't attempt to explain that further.
Then I ctrl-dragged the calculation onto the filter shelf, replacing the original instance, to ensure the filter had the same partitioning as LoD.
Now it seems to work.
top_x_problems_rl.twbx 149.9 KB
I can't quite remember how I set the worksheet up, but I'm pretty sure I just dragged the calculation straight to the filter shelf. It may well have been on the LoD shelf at one point ;-)
I did at one point try to add (or re-add?) the calc to the LoD shelf too as I remember seeing some similar problem in another post, I also tried the trial and error technique to adjust the partitioning, however, I couldn't get it to work - I now know that was because I didn't replace the original instance and thus must've had mismatched partitioning between the calc on the LoD and filter shelves.....
Many thanks for the quick response
I spoke too soon, this one continues to haunt me.....
I now have the correct number of bars, however, ther's still something wrong with the calculation.....
For 2010 data, when I move the slider to show the Top 7 markets, I see the bars in the following order (USA, Japan, Germany, UK, Italy, China, Canada), however, when I move the slider to show the top 8 markets, France appears as if by magic between Germany and the UK (hopefully this is its rightful place)....
Any ideas on the best way to go about tracking down the errant bit of the partitioning ?
Hmmm - well I did only say "it seems to work". ;-)
I've had a quick go and I can see why it's not working. I can even get a table calculation which works in a tabular sheet. But so far I've failed to make it work in your bar chart.
Clearly this is at the outer reaches of my table calc understanding. I'll try to explain. Maybe Joe can help finish it off.
I've added a few sheets.
"All" just shows all countries, sorted by SUM([value]) so you can easily check if it's working.
"Workings" shows the detailed data and has a few fields which explain what's happening.
There are two copies of a calculated field [Index] which just returns the value of INDEX() - but with different partitioning.
The first of those has the partitioning I used in my first attempt. You can see that France is ranked 8th on that index, even though it is 4th on SUM([value]). That's why France drops out when you set the limit to 7.
The next copy of index lets you see how the previous rankings are derived. United States has the 1st, 2nd, 3rd and 4th ranked data, so US is the 1st ranked country. Japan has the 5th, so comes in 2nd. US has 6 then Germany has number 7, so Germany is 3rd country, etc. France's top data item is 21, so comes in in 8th place.
Not to be outdone, I defined my own ranking calculation ([Calc Index]), using PREVIOUS_VALUE(), incrementing on change of value. That works fine using Table (Down). I also defined a variant of [In Top X] referencing the new index.
But then when I tried to apply it to your bar chart, it all went wrong. This is because the data is no longer laid out in a table structure, so "Table (Down)" doesn't work. I've had another go at the trusty old trial and error approach, but this time it kept coming up with error. (Actually it did last time, too, of course, it's just that it looked plausible last time, now it doesn't.)
top_x_problems_rl2.twbx 190.2 KB
I'll be able to look closer into this situation later.
Richard, I think I finally see the patter of this situation. I know we have seen this before in our talks, and I think I finally see the resolution.
The answer is pad the data.
In the attached workbook, you can see the new connection that I made is a join between two sheets, one your data, and the other is a list of all possible combinations of "County" and "Data", your dimensions in use on the worksheet. This creates a dataset for Tableau that has every possible combination available for Tableau to process and sort in the table calculation. This is for addressing purposes. This allows your table cal filter to function as you want.
top_x_problems_jm_edit.twbx 917.9 KB
I haven't looked to see what you did in detail Joe - but the answer's wrong!
The bar chart values are way too high (total length of USA is ~950K rather than 95K) and also the order is wrong. France should be 4th and it's 5th.
This one is proving deceptively tricky.
that is because I did not add on the year filter.
with year filter applied, attached.
Also I am trying another method based on Robert's comment at The specified item was not found.
But the latest one you posted no longer has the top X filter applied. If I re-add that, with the same indexing as you had in your previous version you get 8 country rows not 7, with a few data values for Russia shown in the 8th row.
I had also tried Robert's approach - but the problem is that without data padding it indexes the countries independently for each data value - so if you pick the top 7 you also get some of Russia's data values sneaking in, as an 8th (incomplete) country row again.
okay, got it sorted now.
I am just learning this method, and got it to work in one workbook, and remembering all the steps to make it work in another is not so easy. So I made mistakes.
I do have a more firm understanding of what I am doing to the data to get the desired result now though.
I fully expect the attached is now fixed and working as expected.
Thank you both so much for your help.
Richard - It took me a little while to work through your table calc and I'm not sure I yet know enough to fully understand the PREVIOUS_VALUE part.
Your Workings sheet was of great help in identifying where the calculations were not behaving as expected. I duplicated the sheet and tried to re-create my original bar chart, by deleting the extraneous fields from the row shelf and then using the Show me button to create the bar. A quick switch of the axes and apply the labels et voila. Looks like the 'calc (index)' and associated 'In Top X' have done the trick.
Touch wood, it seems to have fixed it....well it matches the 'All' Sheet, so I assume it is fixed, unless I'm missing something.
FYI, I've called the duplicate sheet 'Workings Chart' in the attached workbook optimistically called top_x_problems_fixed !
top_x_problems_fixed.twbx 919.8 KB
I am unable to recreate what you made there. Can you please walk me through the steps that you performed? I am most interested in how you were able to setup the addressing for the table calc to nothing.
Yes, I want to know that too. That is exactly what I was trying to achieve when I made that extra calc, but it is not possible to save the Advanced dialog in that state (the OK button is greyed out). I often want to do this - so it seems you must have stumbled upon some secret back-door that neither Joe nor I (at least) knew about.