Tableau Small Multiple Line Charts/ Trellis Charts- Complete Tutorial using table calculations & LOD
Вставка
- Опубліковано 21 лип 2024
- #tableau #analytics #lod #calculation #tablecalculation
Creating a Tableau small multiple line chart (or trellis chart) using a single dimension? Or if you have one, have you double checked that the charts show the correct values?
This video is a complete, comprehensive step-by-step tutorial. This steps you through what Trellis charts charts are, typical ways to do it in Tableau, and how to do it using just a single dimension. I will also show you how to spot when it's wrong (and why it's easy to get it wrong), and explain the steps required to fix it.
This tutorial also incorporates many Tableau features and functionalities:
- advanced table calculations (by adjusting addressing/partitioning)
- level of detail expressions (or LOD expressions)
- parameters (for dynamic number of columns)
- different shortcuts and tricks
Sections:
0:00 - Intro
0:05 - What is a small multiple (or Trellis chart)?
1:41 - Simple small multiples in Tableau (using different dimensions)
3:00 - Overview of single dimension small multiple demo
3:36 - Overview of high level steps for small multiple line chart (using single dimension)
4:02 - High Level steps: Matrix or Grid
4:28 - High Level steps: Label for each panel
5:02 - Explaining INDEX()
7:38 - Explaining Columns
10:00 - Explaining Rows
12:49 - What usually goes wrong with Small Multiple Line Charts (with continuous date field)
17:26 - Creating labels for trellis chart
21:33 - Displaying labels for trellis chart
22:59 - Final touch ups, formatting, showing parameter
Related Videos:
Tableau LOD (Level of Detail) Expressions Explained - FIXED, INCLUDE, EXCLUDE - Complete Tutorial
• Tableau LOD (Level of ...
10 Tableau tricks you should know
• 10 Tableau tricks you ...
Learn Tableau Basic Calculations (Calculated Fields) in Tableau (with 10+ examples) | Part 1
• Learn Tableau Basic Ca... - Наука та технологія
I rarely comment but this is the BEST video on trellis charts. Add another subscriber to the list
Thank you so much for your feedback blue817. Very much appreciated!
@@sqlbelle Underneath the subcategory name I would like also like to add % change for the last 30 days and the most recent sales and I have LODS that work but when I try to add them to text they do not show. Any ideas or calculations I try?
Hi @blu817
I have added a quick "most recent sales" calculated and added to the label. I captured some screenshots here. Would you be able to give this a try? I also had to adjust the font for the text underneath sub-category:
www.dropbox.com/s/o6t81nw19oud3mm/Most%20Recent%20Sales%20-%20Trellis.pdf?dl=0
I think a similar strategy should work for % change, but do let me know and happy to help look into it!
Dear, wonderful human behind the name sqlbelle, you are the most gifted teacher. 💖🏆
May your channel grow and flourish. Be blessed! 💐
I very much appreciate the kind note and wishes, thank you so much!
Wow! you saved me tons of research figuring out why i see overlapping dimensions in the index() thank you so much! great and detailed tutorial!
Excellent, excellent tableau tutuial. My everyday watchlist!
Thank you Jing! Appreciate your support, and glad you are finding the videos helpful!
The way you explained things genuinely got me off. What an amazing job, WOW! Keep it up please 😀
You taught here lot more than Just Trellis Chart.... Amazing. Big Thanks.
Thank you Krishna!
Brilliant! You are good at making complicated look easy. Please keep them coming and thx for all your time. Today I just pushed the site bell 🔔 so I won't miss ur next videos.
Thank you for your kind note, abderrahim! Much appreciated! :)
Terrific! Learned foundations and techniques that go way beyond Trellis charts.Thanks much
So happy to hear John! Thank you for sharing your feedback! 🙂
Your training videos are exceptional.
Thank you Terry 🙏
you are so good and clear. love your work. many thanks
Thank you so much for your kind comment, thank you Henri!
Great tutorial.
When I create small multiples like this I always like to use rank rather than index so that I can start with the chart with the largest window_sum total, and end with the smallest, rather than maintain the default alphabetic sorting
This is brilliant, very innovative. Thanks for the video.
Thank you :)
Thank you Going through your all vidoes :) and they are really awesome, still pending with few.
So happy to hear, thank you 🙂
Its the best video . Thank you 🙏❤️
Thank you so much for this tutorial. For me, it's the best tableau tutorial on youtube. I tried to create this chart using Andy Kriebel's tutorial, but I was having an issue (multiple lines in one chart). So thanks to your video I understood that it's because of missing dates.
Thank you so much for leaving this note, Elmurat! I am so happy to hear you were able to find the video helpful in clearing up any challenges that come up with small multiples :)
Simply loaded and amazing...Mindblowing
Thank you Bhawna!
This is so helpful. Thank you for the video.
So happy to hear, thank you James!
This is the neatest and clearest video I have found on Trellis. Thank you for taking the time to explain the calculations in detail!
Thank you for your comment Racky! I am glad to hear it was helpful! 😊
Hi @@sqlbelle , quick follow up question. So I am working on a similar dashboard. I am already using dual axis for my charts so I can't use dual axis for my labels. Is there another way around it?
Hello Racky,
I will have to test this out, but I think it is also possible doing a shared axis chart, and adding the label on the shared axis. I will try this out and follow up in the next couple of days....
Happy New Year!
Donabel
hi thank you for this tutorial. this is what I am looking for
Thank you @sbastianms5156
Cant thank you enought! Amazing tutorial :)
Thank you Micaela! I hope you found it useful 🙂
Awesome.
Thanks for sharing.
Thank you for your note, and thank you for watching gabonicabas!
very nice video ! thank you! :)
Thank you for this blog nice and clean explanation. It will really useful once again thank you
Thank you for your note fakeersab, I appreciate it! Happy to hear you found it useful!
@@sqlbelle Sure it was useful and it was interesting if we got real time requirements.. thanks for blog same added in my public profile..
Thank you so much for this wonderful tutorial. It made what I was trying to create much easier. One question though ... Having followed each step to the letter, I'm finding that sometimes, the labels are not centred. Is there a straight forward fix for this?
This is a very instructive video. It uses simple, easy to understand constructs to create the charts.
I built a chart using these instructions. My data is daily rather than monthly. However, some of the panels are not showing the titles and values at the top. Initially, I thought this was a data issue - no data for the time period means no title, etc. However that is not true as I see titles like "HAY(0)" which obviously means that there is no data for that time period. Any suggestions?
Great Video!
Thank you Peter!
Thank you so much!
Glad it helped!
Nice one...!
Thank you Shailendra
very guuuud👍
U r giving really good Content mam
Thank you Prasad!
Love it!
What if you wanted a trellis chart of descending sorted bars? My use case is to see at a glance all the sales by product category for all states at once and allow the user to highlight a selected product category. The idea is to see at a glance the anomalies.
I can create the trellis chart with a panel for each state containing a bar chart of sales by product category. However, I haven't figured out a way to sort the bars in each panel according to sales for that state. Right now all I can do is sort by the aggregate sales which leaves the states with different sales patterns with their bars out of order.
Ideas?
Very helpful.
Thank you!
Seriously, you are the best. Not kidding. 😄
You are so kind, thank you so much Abhilash!!
This is very generous. It made my day :) thank you Abhilash!
hey actually i learned alot from you and I just given an interview for saama technogy and cleared technical round I am sharing interview question here can you please make a video like how to give an answer to interviewer to build impression like just wanted to say I want focus on body language, answering tact, in what way have to tell answer .
1)itroduction?
2)projects?
3)what types of filters.
4)order of filter
5)data bleanding and relationship difference.
5.1) what is * sign in data blending why it's come and how to remove it.
scenario based question
6)two dimesions
customer id
order date
find the customers who have order in 2014
two ways
in set go to conditin then by formula year(order date)=2014 ===in and out manner result 1st solution
second solution
{fixed [customerid]: attr(orderdate)}
7)find first orderdate of each customer
{fixed [customerid]:min[orderdate])}
8)joins
table1 table2
1 1
2 1
3 2
5 4
null null
inner =
1 1
1 1
2 2
left=
1 1
1 1
2 2
3 null
5 null
null null
right
1 1
1 1
2 2
4 null
null null
full
1 1
1 1
2 2
3 null
5 null
null null
null 4
null null (table2)
9)find the employee with second highest salary
select name from (select name,salary,dens_rank() over(order by salary desc) as rn) where rn=2;
10)find total revenue year wise
select year sum(sales) from customers group by year;
hope you will consider my reply and thanks for everything love from INDIA
❣
Hi, this is awesome. I am able to follow your video up to this part 17.26 min where you are creating labels for your chart. You use a window_sum to provide a total for that particular subcategory section. How would I go about if I need a count distinct for that subcategory (or in my case closed reason)? I am able to follow your video up to this part.
What if instead of Sub Category Sales we want Sub Category Average Sales Price (ASP), with the ASP already being a calculated field. How would we do that? Much appreciated!
A bit late but this is AWESOME! Idk what it says about me that i get pumped up seeing stuff like this but i don’t care lol
:) thank you
Thanks!
Thank you Nesreen!
Hi ! Can someone please help me? After creating the rows column dimension. I am looking to select compute using and the measure with the category I need. However it it nos listed. I am not sure if it is because I am blending two data sources but can someone please guide me? Thanks! I really liked the video.
Great tutorial - however it intrapolates values for dates with no sales (e.g. Bookshelves for Feb 2015). Is there any way to get around this?
thanks !
Thank you, too, Jayasree!
Subscribed. Mam don't forget to upload videos every weekend
Thank you for subscribing Ahamed! I hope you find the tutorials useful.
I want to plot min and max on this chart. Can you help?
Thanks, sqlbelle! Love the way you go into details and explain the concepts. So, a quick question on Trellis charts - is it possible to switch measure values using parameters? So, let's say I wanted to toggle between Sales / Profit & Discount Ratio - letting the user choose the view they want....is this possible?
Hello - we should be able to toggle measures based on parameters, although I haven't tried it in the context of a small multiple. The big challenge here (and where I can see additional work) is when you switch over to a measure that has a different unit. There will be work involved in ensuring the correct units are showing up. It's easier when they're all dollars, for example, or all quantities. Tableau doesn't have an easy way for us to swap units for the axes. But there are other strategies that can be considered (for example, completely swapping out the chart for these cases).
Donabel
@@sqlbelle Thanks and yes, that was my concern too! But, I have a series of KPIs that are all in % - so, absolute units such as currency, quantities are taken care of separately. Would be awesome if you could do a video on that at some point - I think parameters rock, and I've just begun to scratch the surface of the possibilities and to re-iterate its thanks to YTers like you that the passion continues! Cheers!
Thabks miss.
Great video as usual! I have a question: how can I instead display the latest day/week/month (I have a Date Level parameter for this, and a Date calculated field that adjusts the date granularity based on the parameter selection) values per category in the Halfway Mark label? Doing an IF to include values in between two dates isn't working and returns nothing, I guess because of the new date field.
Hi Glenn, please have a look at this, this may help. This displays the latest date, but also has strategies to get the recent month/quarter/year: ua-cam.com/video/N-L26NUXhP0/v-deo.html
Is trellis graph and trellis chart the same?? 'cause I'm a bit confused between these two.
Amazing video! Quick question - instead of showing the sum of sales value above the trend in each cell, how could you show the latest sales value for each line chart? Thx!
Hi Ronan. You can use a table calculation function, like IF LAST() = THEN SUM(Sales) END .. and use this for the label instead. Hope this helps!
Thank you, Yes, this is very useful. 🦋
unfortunately I'm using a mac and i can't do the right click drag in Tableau desktop,
i have not tried to replicate this chart yet, but i wonder if i will be able to make it work given that i cannot do the right click drag
Hi Alicia - I believe the equivalent of the right click drag is command drag on a mac.
Fantastic video. I'm connected to a cube data source which does not allow the FIXED function to be used. Is there a workaround available?
Thanks Scott. Depending on which value you're calculating, there are times when a window function may work, for example WINDOW_MAX or WINDOW_SUM. The other alternative, which may complicate your solution, would be to connect to one more data set with the "right" granularity.
@
sqlbelle can you please let me know how to add the last number on a trend line to the title of each cube? i.e. spot balance as of a certain date and not an aggregated or added balances
Hi Amgad - you want to just add a single number but not aggregated? I think that would be possible, either with LOD calculations or table calc (but ensuring only a single value is displayed - there are a few ways to do this)
If table calc, I would assume a combo of LAST() or LOOKUP() or both.
@@sqlbelle Thanks Sqlbelle!
Great Video! Is it possible to lay one more line within the same category ? If I want to compare the trend with last year's trend, is it doable ?
Thanks Farrukh. I think it might be doable - perhaps with another calculated field and a shared axis chart?
Hi Donabel. Really nice video.
I have a question about nulls. I have nulls showing up at the bottom of the chart. I think it might be related to halfway mark.
If I "filter data" the line charts are gone, if I "show data at default position" nulls are gone but the labels used for halfway mark are duplicated. eg for one colunm/row combo I end up with 3 labels
I have checked all settings and calc and all compute using...all ok but unsure how to fix this.
We have to ignore that. Juat now crossed check in excel
Could you please let me know what are canned reports ?
Thanks Venkata. In what context? Typically canned reports are reports that come with a product. For example if you have a CRM, that CRM may already have packaged 50 reports that you can use right away.
Hello, I need some urgent help with Tableau. How can I contact you?
Great Video. Just a query, sometimes, the axis scales need to be different within the entire chart. Is there anyway to specify this for a particular line ? Thanks
If you right click the axis in Tableau, you can set independent axis ranges for each row or column. Is this what you're looking for?
Yes. That would change all the line charts right? However, from a holistic perspective, that may not be needed, since for the directors, the overall trend is important and the directions they are heading towards. We can always add another chart entailing the corresponding details when one of the peaks is clicked on. Many Thanks.. this is a great tutorial and will surely come in handy. :)
My halfway marks are in the billions. I cannot figure out where I'm going wrong. could someone provide some guidance? Much appreciated!!
can you help getting me the data for the same
Hello Saloni, This data set is using the Superstore data set that comes with your Tableau installation. The version may differ from version to version, but you should be able to use what's been installed in your machine. Hope this helps.
Donabel
Could you add parameters to this chart?
Yes it is possible to add parameters to this chart.
background music is a bit dominating, everything else is good
marry me!!! lol JK JK, but wow I've learned so much with you
:D - thanks Eduardo! Happy to hear you found it helpful :)
Who is she?
She is the devil of tableau 😂🤣