Hi Andy, could you explain the math behind the columns and rows calculations? Why does writing the formulas that way give a square with optimal grids? Could you point me to some videos/resources that explain the calculations? Thanks.
Andy, You trellis on a Dimension. Is it possible to trellis on [Measure Names] so each small multiple is a Measure? I tried your methodology and it didn't work b/c [Measure Names] isn't recognized as an option in the Table Calculation Specific Dimension list. Do you think this is possible? If so, it would be a really cool, powerful, and elegant way to build out a trellis of KPI BANs.
No you can’t because measure names isn’t a field in the data. It’s more like a placeholder. Measure names and measure values can’t be used in calculations.
This hack for "trellis chart" is really slick. It's amazing how you can keep impressing me with your videos. I'm surprised Tableau didn't add this "Trellis chart" functionality yet. The second hack around filling up empty dates with an attribute is great too. Does it work only with dates? or any dimensions? Can we use this to add 0 in a table when there are missing values? Thanks so much again for sharing your knowledge
I've only been able to make it work with dates. In the second example, you saw how I had to convert the year to a date. I had to do this because Tableau "knows" how to fill in dates that don't exists between marks. I have not been able to get it to work with any other dimensions for this reason. For dates, you CAN show 0 for missing values. Add ZN() around your measures, e.g., ZN(SUM[Sales]) and then turn on the Show Missing Values option on the date field. I can create a quick video if you like.
@@vizwiz Correct I've been using the Zn() function and also made sure to use "else 0" in my if statement. Filling the dates is a nice trick. Thanks for sharing. If I understand correctly this doesn't work for other dimension, correct? Alternatively, in SQL I can use a full join to make sure all possibilities are there based on the various dimensions. But it's a pain and the data set becomes humongous.
Thank you for this great video, I finally learned what I did wrong! Five minutes ago I ordered your book in Amazon, since I am a big fan of your work! Thank you for sharing your knowledge!
@@joo6110 Hi. Probably very late but I solved it through formula. Let' s say we have data from 2010 to 2020. Then ideally text would be centered at the year 2015. So I created field such as - IF str([Year (Date)]) = '2015-01-01' THEN [Year] END. Let's call it centered label field. Basically for each observation that is in 2015, you would receive 2015 as output and Null otherwise. Then you put this centered label field and your actual label field (f.e state or country name) onto label of your secondary axis. Choose Min/Max, Cell as scope and YEAR(Centered Label) as your field. Voulla, you will your labels right in the middle :)
The only way you can get it exactly in the middle is if you have an odd number of dates. Then you'd have to write a calc to find the one in the middle and display the label there. If there is an even number of dates, then it will always be slightly off center.
