User want to see top 10 Products per Subcategory
Let’s Build the View
Step 1: Drag “Product Name” to Rows, “Sales” to Columns and sort Product are descending by sales (by clicking on this Icon : )
Step 2: Drag Subcategory to filter and click on show filter
Step 3: For all “Sub Category” Selection, the top 10 products are
Step 4: uncheck “All” and select “Accessories”, the top 10 products are
Step 5: Now let’s apply product filter to get Top 10 Products. To do that drag Sub-Category to filter and use the Top Section to select 10 products
Step 5: with “All” selection in subcategory filter, you will get the Top 10 Products.
Step 6: Here comes the scary part, now change the sub-category filter from “All” to “Accessories”
Woah!! whats happened We are not getting top 10 Products for “accessories”.
Now let’s see the what will cause this
Tableau has “Order of Operations” – which gives info about filters applying for an order.
Now Let’s Concentrate our Scenario, we are applying Dimension filters and Top N Filters.
As per the Order of Operations, Top N filters are applying first and after that Dimension Filter
In our Case Dimension filter is “Sub Category” and Top N filter is “Product Name”.
First Top 10 filter, is getting applied which give top 10 products overall that’s why when we select “All” we are getting Top 10 products
Let’s see how our SQL query will give :
Select top 10 Product_name, Sum(Sales) from Orders
Once we got the Top 10 Products overall, then our “Accessories” Dimension filter will apply
( To Cross Check I have dragged the subcategory dimension next to Product Name)
If we subcategory Column, we don’t have Accessories at all, once u select since no accessories product is available our Sheet is coming as Blank.
Let’s change from accessories to Binders then our view will product names like in below view
Now we got Binders products which are in Overall Top 10.
SQL Query: Select * from ( Select top 10 product_name, sum(sales) from order) a where Sub-category = “Accessories”
So, How to resolve this we can do it in two ways,
- Changing Dimension filter to Context Filter
- Using INDEX() or FIRST( ) or LAST ( ) table calculations
Let’s see this resolution in Detailed steps in coming Posts…