Tableau Tutorial - Top 6-10 LOD Expressions - Practical Examples (Part 2 - LOD calculations)

#tableau #analytics #lod #calculation
This is the second of a series covering the Top 15 Level of Detail (LOD) Examples, based on the Tableau Whitepaper written by Bethany Lyons.
Each use case is explained in detail. Examples and explanation will vary from the original examples in the white paper. There will also be added examples showcasing other types of calculations (regular calculations, table calcs), as well as using groups, shortcuts, and different kinds of charts.
The video also walks you through some strategies you can use to check and confirm if your calculations are correct.
Sections:
0:00 - Intro
1:55 - Use Case #6: Comparative Analysis
8:51 - Use Case #7: Average of Top Values
12:32 - Use Case #8: Actual vs Target (which products didn't meet target?)
19:05 - Use Case #9: Sales for First Day and Last Day of Month (plus difference)
23:37 - Use Case #10: Return Purchase by Cohort (how many customers lapse (ie didn't come back)?
32:14 - Preview of Part 3 of LOD Examples series
Accompanying Workbook to this Video:
public.tableau.com/app/profil...
Whitepaper: Top 15 LOD Expressions (Practical Examples)
www.tableau.com/about/blog/LO...
Need an LOD Refresher? Here is a complete and comprehensive tutorial
• Tableau LOD (Level of ...
LOD Series:
Part 1 - Top 1-5 LOD Expressions - Practical Examples
• Tableau Tutorial - Top...
Part 2 - Top 6-10 LOD Expressions - Practical Examples
• Tableau Tutorial - Top...
Additional Related Videos:
10 Use Cases for Parameters
• Tableau Tutorial - Par...
Tableau Calculations Series in sqlbelle channel:
Part 1 - Basic and Aggregate Calculations
• Learn Tableau Basic Ca...
Part 2 - Level of Detail Expressions - FIXED, INCLUDE, EXCLUDE - Complete Tutorial
• Tableau LOD (Level of ...
Part 3 - Table Calculations
• Tableau Table Calculat...
10 Tableau tricks you should know
• 10 Tableau tricks you ...
Tableau Dates: Complete Introduction
• Tableau Dates: Complet...

[@vijayarjunwadkar]

Thank you Donabel for an excellent video! You have shown very practical use cases in a simple easy to follow manner! Take a bow Tableau Master! 😊👍

[@sqlbelle]

Always a very kind note and support from you Vijay - thank you so much for taking the time to watch and comment. Happy to hear you found the video still informative :) ... Very much appreciate. Please accept my kind and heartfelt thanks :)

[@ManojKumar-qq9lo]

​@@sqlbelles AQ पॉप😢

[@kwemishuza73]

I have been a PowerBI Dev, and now I'm upskilling - Tableau Viz - Your videos are exceptionally awesome, short, simple, and easy to follow. The contents of your video are superb. You are helping to transform into a Tableau Developer role very easily. I want to thank you for all that you do and how you have added value to my tableau journey. I will follow your tableau public to gain more nuggets there too 🙂

[@tamilkumaran368]

Your step by step explanation is the highlight of this video to understand all the LOD concepts very clearly. Thank you for the same.

[@sqlbelle]

Thank you Tamil, appreciate your kind comment! Glad to hear you found the video helpful :)

[@TonyChangTaiwan]

I've learned so much from LOD Part 1 and 2, looKing forward to see your Part 3

[@sqlbelle]

Happy to hear that Tony! Thank you for sharing :)

[@agermoune]

can't wait for the final one, especially the set action. Used a couple of your LOD cohorts for the first, second, and last purchase already at work. You are making us shine, getting visibility and hopefully, a raise that will help with this crazy inflation :-)

[@sqlbelle]

Thank you Abderrahim, so happy to hear you found the video helpful and that the videos are helping in your work as well :) That is the best consolation!

[@yifanyu6554]

I would say this is probably the best explanation I can find of LOD use cases, looking forward to see the Part 3

[@sqlbelle]

Thank you Yifan! Glad you found the video helpful :)

[@lathbotti5497]

Thank you so much , because are changing my view about tableau . you are the best teacher ever.

[@michafabich4372]

thanks to you I become better and better every day in Tableau :) this tutorials are so concise and substantive that I cannot get over it :)

[@sqlbelle]

Thanks Michal! Tableau's an awesome tool to start with, but there's a lot of little tricks that make it even more awesome. I am learning as I go through their materials too. :)

[@christospaisiou5128]

This video was not only helpful. It was AMAZING. Thank you so much. Very good, very practical and easy to follow!

[@sqlbelle]

I am glad to hear, thank you Christos!

[@indrachandran8115]

Great videos. Thank you so much for your time and effort to make us understanding the concept in depth.

[@sqlbelle]

Thank you Indra!

[@michafabich4372]

this videos are legendary! thank you for your effort. really appreciate it.

[@sqlbelle]

Thanks Michal! Appreciate you taking the time to watch and leave kind comments :)

[@thaodo817]

I'm keeping on watching every new video of you. It is extremely useful for fresher like me. Thank you for your hard work!!!

[@sqlbelle]

Thank you Thao! Glad you are finding the videos helpful!

[@ambujrai7355]

Thank you for sharing one more useful video related to one of the most important topic in tableau... Your explanation is excellent.. thank you once again... 🙂

[@sqlbelle]

Thank you Ambuj! Happy to hear you found the video helpful :)

[@nadeemraja655]

Learned a lot and looking forward to part 3. I would also like to see a separate series of videos on more complex scenarios with nested LOD to add more complexity. Good job & Thx

[@sqlbelle]

Sounds great, thank you for the suggestion Nadeem! :)

[@ShadSaleem]

Thank you very much for taking time explaining all the details your explanation was excellent in understanding LODS

[@sqlbelle]

Thank you Shad, I appreciate your comment!

[@juliayang7345]

This video is very useful👍, I have learned a lot of analytical methods and technology in Tableau

[@rakesh4642]

Actual vs Target example was way impressive 🤓keep rockin

[@sqlbelle]

Thank you Rakesh!

[@thomasshelby8572]

Thanks for one more useful, easy to understand video! 🙂

[@sqlbelle]

Thank you Thomas! :)

[@kamranzamanni6826]

Thank you for the informative video!

[@sqlbelle]

Thank you Kamran! Glad to hear you found the video informative! :)

[@paulocarapinheiro8739]

Thank you! Very well explained. Kind regards

[@sqlbelle]

Thank you for your comment Paulo!

[@nallelybalderasacatitla9102]

Thanks for explain LOAD step by step, you're awesome

[@sqlbelle]

Thank you Nallely..

[@rnaresh1687]

Excellent Explanation !! Please upload complex scenarios also.. Thank you so much

[@Guna_cocktail]

Thank you so much for this LOD concepts

[@sqlbelle]

Thank you Guna!

[@youmunna6452]

Thanks mam...your my favorite tutor...

[@sqlbelle]

Thank you You! Hope you found it helpful :)

[@irshadkhan-fv5nt]

Awesome !!! this is so perfect.we appreciate your hard work in making these valuable contents. Please make some contents on connecting Tableau to MySQL,SQlite and SQL-Server.

[@sqlbelle]

Thank you Irshad!

[@MrAstonmartin78]

Very cool & usefull

[@peterparker4358]

That's was super cool 😎 thank you so much mam again..

[@sqlbelle]

Peter, Peter - always a big thank you for your support. I cannot tell you how much I really appreciate all the comments, support - just a big, big thank you :)

[@chitranshsagar2456]

Too Great explanation :)

[@sqlbelle]

Thank you Chitransh!

[@dikshachourasiya9749]

Thankyou for sharing 👍👍

[@sqlbelle]

Thank you Diksha!

[@sudarsanreddys9060]

..Im thankful to U And For Waiting for part 3 lods

[@sqlbelle]

Thank you Sudarsan!

[@srt5806]

Thank You for the video :-)

[@sqlbelle]

Thank you :)

[@neelnickkey4865]

Thank you for your efforts. !!!c

[@sqlbelle]

Thank you Neel! :)

[@cococnk388]

With LOD we can answer so many questions thanks ...

[@ashishroshan5946]

@sqlbelle for the LOD 7th can we use this expression AVG({FIXED State,Customer ID: Max(Sales)}). Post which in the map we will select state and this LOD function

[@RameshCh-me8jm]

Its so good 🙂

[@sqlbelle]

Thank you! Hope you found some practical use cases you can use right away :)

[@surajthallapalli4227]

The Best !!!!

[@sqlbelle]

Thank you Suraj!

[@user-re5uv2mm2h]

Super good!!!! Thank you! Liked and Bookmarked (already subscribed ;) )

[@sqlbelle]

Thank you Jers! :) Glad you found the video informative :)

[@svetlana_kapranova]

Great series of videos!! I'm learning a lot! I only have a silly question: where did you get the coffee chain dataset? I googled it and I found some, however none of them have the "Target" fields.

[@sqlbelle]

Thank you Andréa. I have a link to the workbook in the description. You can get the dataset from there - when you open it in your Tableau Desktop you can save it back to a csv (or Excel). Hope this helps!

[@svetlana_kapranova]

@@sqlbelle Yes, it helps! Thank you!

[@conlele350]

Hi Donnabel, thanks for your excellent tutorial, I just have a question, in case 6 you use Window_sum for counting number of sub-category if it has more sales than Accessories, but in case 8 you only use Sum for counting products that met the target or not. Can you give me more elaboration in this case. And its even better if you can make a video to further drill down on when to use window_sum, window_min, window_max... rather than min, max, sum... it-seft. Thanks

[@musavvarali5671]

Great

[@sqlbelle]

Thank you Musavvar!

[@muruganselva7431]

Thanks for your Effort. Could you make the videos for "How to improve the tableau dashboard performance" 1)Desktop level 2) tableau server level, 3)Database level

[@sqlbelle]

Thank you Murugan for your suggestions!

[@conginhnguyen7377]

Why in 6 example you use Window_sum while in 8 example you use SUM for calculating numbers of subcategories/products basedon conditions?

[@bhawnamongia4955]

What if -have to show product category against different Cohort dates as in (First Purchase , 01st time bought Product category , second purchase, 02nd time bought product categories and so on.. ). Looking for this scenario, Thanks!

[@ashishroshan5946]

sqlbelle when I selected only states in the map none of the colors or tooltips were visible when I hovered over it. However, it became visible post I added country. What is the reason behind it?

[@MashiroRedo]

Omg simple but so useful! Thank you. Are you a Zen Master?!?!!!

[@sqlbelle]

Thank you Mashiro! Glad you found it useful :) .. No I am not a Zen Master :)

[@PatrickBateman12420]

I wouldn't overrate the title of a "Tableau Zen Master," a title giving to people who educate other users. You might be the best Tableau user in the world, but nobody knows you (except your customers).

[@shailendrakumar08]

Nice

[@sqlbelle]

Thank you Shailendra!

[@pradeepp9126]

I used a bit different approach for Comparative analysis, But your's is much more cleaner and efficient Difference : SUM([Sales]) - SUM({FIXED : SUM(if [Select Sub-Category] = [Sub-Category] THEN [Sales] END)}) I believe, The best way to learn is to try reaching the final Use case before checking the tutorial, So that we would come to know what are the different approach we can use to solve the same problem.

[@sqlbelle]

Thank you for sharing Pradeep!

[@madhusmitapanigrahy6835]

Hi, I am unable to get the option "Assume Referential Integrity'' on my data menu for my dataset. What could be the reason behind this? What are the cases when we don't get to see this option in our data menu in our data source and what are the case we do? It would be a great service if this query gets answered. Thanks in advance! Please answer

[@ritikajaiswal3824]

Can you provide the transactions coffee dataset that you used?

[@ibrahimboyac7065]

Thank you Donabel, I would like to ask about top 11-15 LOD video. I couldnt find it. Did you add before or you didnt add that video. Thank you again.

[@sqlbelle]

Hello Ibrahim! I was only able to finish until 12. 11 to 15 were quite long so I've put them in their own videos. I still have 13 in draft :( Here's 11: kzbin.info/www/bejne/Y5qXaKiVo85_jpY and here's 12:kzbin.info/www/bejne/hF6vY2mEir2bhpI Glad you have found the videos helpful!

[@holeyshit]

Very good video, can't find part 3

[@sqlbelle]

Thank you Prashant - all the videos to date are in this playlist: kzbin.info/www/bejne/r6bWmWSvg99sjK8

[@ajaytawaniya7915]

Thanks for Sharing with us very valuable content. It is very useful in day to day work. But i have question like in use case 6 you use window sum can we use sum in the place of window sum.As you used sum in use case 8

[@sqlbelle]

Thank you Ajay. When we have an aggregate already (for example if data is already summed up_, and if we want to do another sum, we will need to use a table calculation function like WINDOW_SUM (unless you are using a FIXED LOD, which technically converts data into a scalar value and therefore can be passed to SUM)

[@ajaytawaniya7915]

@@sqlbelle ❤️

[@pradeepp9126]

Hi Sqlbelle, Which LOD is performance efficient (INCLUDE/EXCLUDE/FIXED), Please can you help had some performance issue in one my dashboard, I am trying to optimize it (Need to choose between INCLUDE/FIXED).

[@sqlbelle]

Thanks Pradeep .. I'd still say it depends since the behavior of INCLUDE and FIXED are still different. How should your calculation behave with filters? Sometimes it can become difficult if you add all your filters to context all the time if you want the FIXED to be affected by filters. It's also hard to qualify which one will be more efficient - will depend on how complex your calculations are, esp if they're nested LODs. In general though I would assume FIXED is faster since it's an independent query, much like when you have a regular subquery in a SQL SELECT statement. Hope this helps.

[@pradeepp9126]

@@sqlbelle Thank you sqlbelle, understood that it totally depends on use case, I did a performance recording using both of them didn't see much of a difference, the calculation was a simple one with out any nested lod's Sum{Fixed [tenure] : avg(total area)}, Majorly, I think need to focus on pre-aggregating the data instead of using lod, thanks a lot for your time you are such an amazing mentor...👍

[@sasmitadash492]

Hi, thank you so much once again. Could you make a video on 'window-function' keys.?

[@sqlbelle]

Thank you for the suggestion Sasmita!

[@sasmitadash492]

@@sqlbelle it's my pleasure, but in same time I need to improve myself. I need your support. Thank you so much once again.

[@jawadahmad5714]

When I tried IF category = parameter. category the then sales end. the results doesn't match exactly with the sales amount ut is always greater than the sales value. can you please tell me why. I am not using the superstore data for it.

[@princessdalima1328]

I'm stuck in the Q10. When I'm dragging the customer id to the plot it is not changing into measures( green color) . Help..

[@seanrehder]

Do LOD fields work if my data source is a live data source?

[@AbhishekKumar-xd3qq]

mam could you please calarify in use case no 9 while iam dragging first month sales and last month sales iam getting cntd(first month sales ) as well as cntd(last month sales .how shall i get sum (first month sales ) and sum(last month sales )

[@dattatrayashinde4946]

Thanks 🛅

[@agelee89]

I would like to ask when do we use WINDOW_SUM? In one example it was needed, why we couldn't use just SUM?

[@sqlbelle]

Thanks agelee89 - WINDOW_SUM will need to be used if what you're trying to get a total of is already a SUM (or another kind of aggregate)

[@l.kennethwells2138]

Is this dataset available to follow along?

[@mbem6725]

can i know the link for coffee chain dataset? so i can download it

[@sasmitadash492]

Hi belle, I just love how u teach a subject. Kindly could you solve my query?? My query is what is the difference between calculated fields and para meter'.

[@sqlbelle]

Thank you Sasmita. I have a video on parameters that may help clarify: kzbin.info/www/bejne/ZobEo5J6apyEnrc

[@sasmitadash492]

@@sqlbelle thank you so much.

[@Manikandanbalan]

Can you please upload videos on interview questions

[@sqlbelle]

Thank you for the suggestion Mani!

[@786gohan]

💙

[@sqlbelle]

Thank you Pushkar ❤

[@ravisharma3069]

Can some help me please at 7:59 why used window_sum and at 16:19 why used sum ?

[@sqlbelle]

Thanks Ravi. SUM is an aggregation function, WINDOW_SUM is a table calculation function. We use SUM() if we want to see just grouped values for example total sales per state (which is at 16:19 In 7:59 - what we want is a grand total count which we will display in the title, and not simply a difference per state. Note that the chart is subdivided by State (ie this pill is in your Rows shelf). What we want to get is the total for all the values shown on screen, which is why we need to use a table calculation function WINDOW_SUM. I encourage you to give it a try to see the difference.

[@ravisharma3069]

@@sqlbelle Thank you Donabel for your quick response and now its very clear where to use SUM and where to use Table calculation. l really appreciate your hard work and the way you are helping and you made LOD very simple for everyone i am going through all your videos and found very useful . God bless you Donabel

[@lillresonates7081]

Madam can u make a video about calculations

[@sqlbelle]

Thank you Venugopala. I have a playlist on calculations if you'd like to check it out: kzbin.info/www/bejne/h5rQh4Siaqaij68

[@lillresonates7081]

@@sqlbelle thanks ❤

[@ben-cb5er]

Tableau has new certification!!!!! Certified Data Analyst.... please help lol this just sound so hard to pass :(

[@sqlbelle]

Thanks Ben! I havent taken the new beta exam yet, but I am looking at the new prep guide. I'll plan to create a new video addressing it.

[@user-lj7bo5dh1x]

Good videos and explanations but they go so quick that I find myself having to pause constantly and rewinding which was a bit frustrating.

[@akjiang]

I am not sure your Use case #9 is correct. 1/3/2018 sales is only $16, not $14,237 which is for the whole jan 2018. could you please double check? your example displays the first day of the month, but the measure still calculates for the whole month

[@sqlbelle]

If you watch the video, I emphasize that $14,237 is for the full month. A succeeding step is to extract the first day of month (using INCLUDE LOD) and last of month (using INCLUDE LOD). The crosstab walks through how you can extract the information. However it's the charts later on (around 22:39) that uses the extracted information. Note that with the crosstab, the actual sales were not displayed. I encourage you to view the video again. in 22:39 you will see that the first of month and last of month sales are different (and NOT $14K). And as you say, it displays $16 for first day sales. If you check the accompanying workbook, or re-create the charts I create - you can verify that the values are accurate.