April 18, 2017
Tableau Tip Tuesday: FIXED Level of Detail Expressions in a Plain English Sentence
UPDATE: In the first iteration of this video, I was explaining the FIXED LOD like I would explain an INCLUDE LOD. I have fixed that and re-recorded the video. Thanks to Graeme Wiggins and Bora Beran for the feedback!Back in February, I wrote about putting table calculations into an english sentence. This concept can be easily extended to level of detail expressions. As such, I'm going to kick off a series of blog posts that show how to take ANY level of detail expression and turn it into an english sentence.
First up are three use cases for FIXED LODs.
How many customers placed how many orders by region and product category? |
How many products sell in how many regions by year and quarter? |
What is the total number of customers acquired by region by day? |
Here's a video of how I created each of the scenarios and the examples for you to download and follow along. Enjoy!
Subscribe to:
Post Comments
(
Atom
)
Great work, Andy! I'm curious how this would change if you were fixing to more than one dimension, though. For your first example, how would it read differently if the calculation was {FIXED [Product Name],[Manufacturer]: COUNTD(Order ID)]}? These kinds of LOD always confuse me a little.
ReplyDeleteSimple, just add each dimension after the FIXED to "for each". So in your example, for each product and manufacturer, calculate the distinct number of orders.
DeleteThis is extremely helpful. These have always been mysterious, black-box, voodoo magic to me.
ReplyDeleteI'm looking forward to the rest of the series. Thank you!
Hey Andy, nice post, but I just wanted to feed back how I interpreted your description of the LOD because the way I read it is different to what the LOD is doing. Might just be me, but just wanted to mention in case anyone else has read this in the same way that I have.
ReplyDeleteWhen you say "For each Customer Name, Computer the Distinct count of orders By Region and Product Category" I read this as calculate the number of orders a customer makes for a specific category in a specific region. For example Christine Kargatis has 1 order of Furniture in Central so I would expect her to be in the 1 'bucket' in the top left bar chart.
However your LOD is calculating the total number of orders a customer makes across all categories and all regions and then displaying this 'total' in each category and region that the person has at least 1 order.
In your example Christine Kargatis is being displayed in the 2 bucket in the Furniture in Central even though she only had 1 order in Furniture and Central (the other order was in Office Supplies).
Do you see where I'm coming from?
You would need the following LOD for how I interpret your description
{ FIXED [Customer Name],[Region],[Product Category] : COUNTD([Order ID]) }
Correct Graeme. I will re-record the video. I was reading it like an INCLUDE. Always learning!
DeleteFirst LOD example computes total number of distinct orders per customer name regardless of the region and category for the orders. E.g. person X might have had 10 orders, spread across 2 regions and 3 categories. But result of the LOD calc for Person X would be 10.
ReplyDeleteThen it uses it as a dimension with a COUNTD(Customer Name). So the height for the mark corresponding to 10, Furniture, Central would show the number of customers who purchased Furniture in Central region and made a total of 10 orders overall.
Second example is similar e.g. the bar 4, Q1, 2012 is the number of distinct products sold in Q1 of 2012 out of the products that historically sold in all 4 regions.
Behavior of calculations can change based on functional dependencies between fields and what is in the viz. E.g. computing {fixed sub-category : sum(sales)} and using it in a viz that has category as dimension (where each subcategory only belongs to a single category) will have a different behavior than {fixed city : sum(sales)} being used in a viz with State dimension since there are many cities that share the same name in different states. To have this behave like sub-category, one would need to write {fixed city, state : sum(sales)} so cities can be correctly distinguished.
Thanks for the clarifications Bora. This goes to show how overly complicated Tableau has made LODs and how much easier they need to be made. I'll redo the video soon.
DeleteGraeme and Bora, thank you for your feedback. I have updated the post and the video.
DeleteVery useful post video and comments appreciate the effort of everyone
ReplyDeleteAndry can you video for include and exclude as well. Its confusing to me. Lets say in plain english
ReplyDeleteAt some point I probably will. For now, if you use an INCLUDE, you add those dimensions to the FOR EACH part. If you use an EXCLUDE, you remove those dimensions to the FOR EACH part.
Delete