This is a great question and one I've pondered before. I've just written a blog post about it and hopefully the example there explains how you can create trellis charts:
Is this solution what you were thinking?
Thanks for the response. I am getting close using the page that you sent. Although I am having issues with the Created Field for Column. It says that the
'%' function cannot be applied to table calculations and fields from multiple data sources. Any ideas of how to get around this?
@Jeremy: % can only be used with Integers in Tableau, so try sticking your calc field in an INT(...) wrapper.
The multiple data sources bit might cause you to come unstuck. If your data is in two datasources, and Tableau still says that's the problem, I think you might be in trouble. Can you post a sample workbook?
The problem appears to be caused by the index function. If i simply have a numbering system in place instead of using the index function then everything works fine. My problem with that is that This makes the positions fixed. And with my data I may have 100 different panels and only want to display 4 at a time. So making it 10 by 10 is too much. And with locking them into certain positions it will require to the whole 10 by 10 grid to be shown. Happy to provide more info
I'm not quite sure from your example what problem you're having, but it could be related to the problem I had when I had a look at this yesterday. I actually started putting an example just like Andy's together, but got stuck, as I explained in a comment on Andy's blog post.
The index on [Customer State] works fine as long as there is at least one order per year per state. But if you want a more fine-grained timeline, it can become sparse, which means the index value doesn’t stay consistent for each state. I haven't figured out a good way past this yet.
I've attached my example workbook which demonstrates the problem.
trellis.twbx 1.1 MB
I am still trying to find a good way to explain what I understand, but attached is what I think you are looking for.
Here is my current try, a stream of thought (this is not complete, but these are points that help me ensure table calcs are evaluated as intended):
The way a table calc is evaluated depends on many factors, including:
1. what kinds of pills you have in use
- A pill is either aggregated or a dimension. If you see something like "ATTR"," SUM", "AVG", etc. at the start of the pill it is an aggregation.
- A pill is either continuous or discrete.
- There are four main types of pills:
a. aggregated continuous
b. aggregated discrete
c. dimension continuous
d. dimension discrete
2. What shelf these pill types are on
- each of these types has a different effect on the evaluation of a table calc depending on their arrangement.
- when you have a dimension on both the rows and columns shelf, Tableau will padd for missing dinension combinations, unless you are using a set based on these dimensions
- building your view with all dimensions on the rows shelf is a good place to start, and once results are as expected, then move pills one at a time to other shelf, and see how shelf placement effects evaluation.
3. How you set your compute using
- if you use the compute using selection from the context menu, or the drop-down in the dialog, and set it to a dimension, that dimension will be used for addressing, and all others will be used for partitioning. I call this Regular Compute Using
- when setting the compute using to something like Table/Pane/Cell, I call that Layout Compute Using, and try to avoid this because I find it to be a delicate setting (move a pill, move a dimension pill, and your evaluation will likely be effected).
- with Regular Compute Using, if all addressing values do not exist in all partitioning values, the INDEX() value for each addressing value will not be the same across partition values. This is apparent with not all combinations of dimension values exist. (In this situation, there are states that have no sales records for some months, so whit your compute using set to State, and partitioning on Month, each State will not have the same INDEX() value in each month.)
- when using a Regular Compute Using and a crosstab of the dimensions in use (one on the Rows, and the other on the Columns), and no Set based on those dimensions, Tableau will pad the data making each State have the same INDEX() value for each Month.
- So without the crosstab setup, but with the desire for Tableau to pad the data to cause the INDEX() function to evaluate as desired, you can have Tableau pad the data as desired with an Advanced Compute Using.
- When you use multiple dimensions on the Compute Using (right-side list box) for addressing, I think of it as like a pseudo-crosstab of all potential combinations of the dimension values (this is what we want in this situation). If you use a Set instead of multiple dimensions, that data will not be padded.
It is my current belief that if you are aware of what pills you have in use, what shelf they are on, how your compute using is setup, and how all these potential combinations of setups effect the evaluation of table calcs, then table calcs are straightforward.
Without doubt, I have not included all information needed to understand table calcs here, but only some key concepts that play a factor in the attached "(fixed)" worksheets.
If you would like to have a more in-depth conversation about table calcs, I welcome your email at firstname.lastname@example.org
I am still figuring this stuff out, and I hope that by sharing the patterns that I am discovering, we can all make use of this wonderful application. My current opinion of table calcs is they are very flexible, but with this flexibility they have become very nuanced, and their evaluation depends on dozens of factors.
trellis_edit.twbx 1.1 MB
In summary you forced it to pad the domain, which it wasn't doing for reasons I have yet to understand - but I'll re-read your notes later.
> "which it wasn't doing"
Because it is not in a crosstab layout (when one dimension is on the Rows shelf, and the other dimension is on the Columns shelf).
The setup is one dimension on the columns shelf, and one on the Color shelf, this layout of pills does not cause Tableau to pad the domain.
Having both dimensions used for addressing (right-side list box in advanced compute using) will provide the effect of Tableau padding the domain (I don't think it is really padding the domain, it just looks like it is). The sort order of these items in the list box is important. You will want State to be the first in the list, and Month to be second, and then set the At The Level to State.
What this means to me is in this situation, there is no partitioning happening, and the INDEX() function will only increment when there is a change in the value of State, essentially ignoring the values in the Month dimension.
Thanks for the feedback. I was able to get it working when there was only 1 sheet in the excel file that I was importing. I have created another example here which has 2 sheets that I am joining based on the dummy variable. This seem to not allow the '%' to be used in calculations. What I want to is be able to panel by the countries. And choose how many columns. My real data has over 20 countries and so i would like to do a 3 by 3 of just the countries selected via the filter. Any help is greatly appreciated. Thanks
Jeremy: The problem seems to be that you can't use the modulus operator on table calculations (such as INDEX()) in version 6.0, for some reason. I've attached a version 6.1 workbook demonstrating that it now works - yet another reason to upgrade.
The reason I came back to the thread was to see if anyone can work out a smarter way to label the panes. I've used this technique several times since seeing Andy's blog post, but I can't find a good way to label the panes. On most of my charts labeling the line ends messes up the layout really badly. I've managed a fairly ugly workaround with a dual axis chart, but it's not very satisfactory.
trellis_example_2_v61.twbx 47.5 KB
That's the effect I'm after. It's a bit of work with the custom SQL, so it would be nice to have a built-in way to do it without going to that effort - but it's good to know there's a way if needs be.
I was trying something vaguely similar - but was trying to derive the X and Y coordinates for the label dynamically. I'm sure it's possible to pick the midpoint of the X axis with an AVG() or something though.
I am not really doing anything advanced in the custom SQL, just duplicating, and adding a field (so I can tell what copy of the data I am dealing with), but I agree, it would be nice if something like this was built-in instead of this kludge.
It is possible to get the year midpoint with some table calcs, but I did not quickly find an elegant method, and there were other issues that I ran into as I tried to make it more dynamic, so I set it static, for demo purposes.