1 2 Previous Next 19 Replies Latest reply on Jan 25, 2013 4:41 AM by Jonathan Drummey

    Filtering Using a Hierarchy - I think?

    Matt Wheelock

      I need to filter and only display certain items based on another filter list. 

       

      For example:  Apples and Oranges; ABC and DEF companies.  I need to display certain products that are attached to specific customers. 

       

      I need to show only the apples ABC company ordered even though they ordered bananas as well.

       

      Then I need to show only the bananas DEF company ordered even though they ordered apples as well.

       

      Point is apples and bananas are shared by both companies but I need to drill down to show only specific products that are attached to each company.  

      I think I need to create a set or a group.  I am nearly positive I need to create some type of hierarchy and filter it.

       

      The issue I am running into is that apples and bananas are on the same field range but lower in the hierarchy so when I choose those apples and bananas it shows how many apples and bananas both companies ordered. 

       

      Any help is greatly appreciated.  I have only had Tableau for a month or so and am not that well versed in it.  Thanks!  

        • 1. Re: Filtering Using a Hierarchy - I think?
          Mark Holtz

          Hi Matt,

          I'm not sure I followed all that. Can you post a packaged workbook? or at least a mockup dataset and what you want to display from it?

          • 2. Re: Filtering Using a Hierarchy - I think?
            Robin Kennedy

            Matt,

             

            It's difficult to give you a proper answer because the examples in your question are a bit abstract but if I have followed correctly then I think you have actually answered it yourself.

             

            What you need to do is create a Set. If you put your two dimensions out, companies and fruit for example, along with the count of orders, then select the items that are relevant - i.e. ABC/Apples & DEF/Bananas by clicking on the number of orders of each one whilst holding the Ctrl key, then right click and select Create Set.

             

            This will bring up the Set dialogue showing the combinations you selected. You can then add the set to the filter shelf and only the items you selected are shown. This set can then be used in other sheets and for other analysis.

             

            Hope that helps. If not then it may help us if you could post a packaged workbook with some of your sample data in it?

            • 3. Re: Filtering Using a Hierarchy - I think?
              Matt Wheelock

              Thanks for quick reply.  I'm sure I have the right idea and did answer myself, but the execution is where I am lacking.  I don't know how to get there. 

               

              In this example you'll notice that Maine's Paper & Food Service and Reinhart Foodservice both share 8220-20 and 8220-30.  McLane Foodservice Inc and Mt Airy Casino Resort share 897320, 897330, and 897335. 

               

              My goal is to have Maine's Paper and Food Service only show 8220-20 and have Reinhart Foodservice display 8220-30

               

              Also McLane Foodservice display 897320 893730

               

              Mt Airy Casino Resort display 89735. 

               

              It's a dummy example but hopefully that clarifies what I am asking.  Thanks again! 

              • 4. Re: Filtering Using a Hierarchy - I think?
                Jonathan Drummey

                Hi Matt,

                 

                You'll need to post a packaged workbook (.twbx). The .twb only includes the view layout and field definitions, the packaged workbook will include the data.

                • 5. Re: Filtering Using a Hierarchy - I think?
                  Matt Wheelock

                  Oops.  Let's see if this one works.  Thanks for the advice!   

                  • 6. Re: Filtering Using a Hierarchy - I think?
                    Matt Wheelock

                    Thanks for the help.  I created a set.  Now my issue is it seems that the option to create a custom list is no longer available.  These products aren't ordered all the time therefore when I do this on a daily basis I want the filter list to have the capability to display all potential items though they may not have been ordered.  I see the existing combinations but no option to create a custom set list.  Any advice?  Thanks!

                    • 7. Re: Filtering Using a Hierarchy - I think?
                      Jonathan Drummey

                      Hi Matt,

                       

                      I still don't quite understand what you are trying to do, in particular when you say that you only want an item to show for one customer but not the other, what criteria are you using to limit that selection? Are you using a Quick Filter? If so, you can click on the quick filter's context menu (the down-triangle in the upper right of the quick filter) and choose "All Values in Database".

                       

                      Otherwise, I'd suggest you post a packaged workbook with your work so far.

                       

                      Jonathan

                      • 8. Re: Filtering Using a Hierarchy - I think?
                        Matt Wheelock

                        Hey Jonathan,

                         

                        I appreciate it.  Sorry for not being more clear. In this example, Let's look at "Systems Services of America" and "McLane Foodservice". 

                         

                        I have isolated 8220-10 and 8220-20 (these are item numbers by the way) solely to Systems Services of America and excluded the others 

                         

                        Same for McLane Foodservice, I have isolated 2330-10 only and excluded the others. 

                         

                        Now the issue is that I need to account for the items that could potentially be on there.  I need to create custom values as one is able to do outside of the "Set" filter feature. 

                         

                        For example let's assume there is also the 8220-35 item that could possibly be ordered for Systems Services of America but was not included in this range of data today but tomorrow it could be.  Rather than search and filter manually everyday, I would like to create the custom filter to pre-define the parameters should that product be ordered but I still need to maintain the set feature it seems, unless there is another way to do it..  I hope that makes sense.  Thanks for your help!

                         

                        Matt       

                        • 9. Re: Filtering Using a Hierarchy - I think?
                          Jonathan Drummey

                          The workbook you attached is the same from earlier, I'm still not clear on what you are doing with your filters. When I create a Set and filter on it, I can still see all the members of the set. See the attached for details.

                          • 10. Re: Filtering Using a Hierarchy - I think?
                            Matt Wheelock

                            The idea is to capture the items could potentially be on the report, not only the ones that were shipped any given day.  On this report all of the items are on there.  Tomorrow the 8220-20 may not be on the report because it was not ordered. 

                             

                            I'm trying to avoid manually creating sets and applying filters daily.  That defeats the purpose of what I need. 

                             

                            I've created templates for my sales team to capture their daily orders and created custom filter lists that display only their individual sale items.  The custom filters remain in place although the specific values indicated on the custom list may not be present for that day's data.  That's what I am trying to achieve using the "Set" feature.  See the following example. 

                             

                            For example, let's assume McLane Foodservice uses 2330-10, 2330-20, 8220-10, 8220-20.  However one sales person gets credit for 2330-10 and 2330-20 while the other gets credit for 8220-10 and 8220-20.  Now let's assume that tomorrow McLane doesn't order any of those items except the 2330-10.  But the following day McLane orders all of those products again.  How do I create a filter that can show the respective products to their respective sales person albeit the same customer with different items that remains in place like the custom lists do?  

                             

                            How can I preset the filters now that the proper dimensions are attached to each other?  Individual dimensions have a custom filter option.  I need to translate the idea of a custom filter list over to a set.   

                            • 11. Re: Filtering Using a Hierarchy - I think?
                              Ey Tan

                              Hello Matt,

                               

                              Would using dashboard actions help?

                               

                              referring to the attached workbook, when you click the cell for an item on sheet 1, extra details will show on sheet 2. To select multiple company+item combis, hold the ctrl key while clicking the cells for the item numbers. hope it helps?

                              • 12. Re: Filtering Using a Hierarchy - I think?
                                Jonathan Drummey

                                Hi Matt,

                                 

                                I'm hoping that what Ey Tan posted is helpful too you, because I still don't have a grasp of what you are really trying to do and what your issue is.

                                 

                                You write, "The idea is to capture the items could potentially be on the report, not only the ones that were shipped any given day.  On this report all of the items are on there.  Tomorrow the 8220-20 may not be on the report because it was not ordered. " I'm not sure what you mean by "report," it''s almost like you're referring to the "report" as the data source and the output. Are you changing your data source each day?

                                 

                                Also, you write, "However one sales person gets credit for 2330-10 and 2330-20 while the other gets credit for 8220-10 and 8220-20." How in the data do you know which sales person gets credit for what?

                                 

                                It seems to me like there are at least two fields that could be in the data that aren't in the sample data, namely the salesperson and the date. If you want to show item X for company Y for sales person Z on date N, then having all of those elements in the data makes life a lot easier. Then you can set up filters by sales person (in Tableau Server if each sales person has their own account you can even filter it such that each sales person only sees their data), by date, etc.

                                 

                                As I noted, I'm still not clear on your goal. Are you trying to show sales that have happened, or sales that could happen, a list of items, or what? For me to help you further, a visual mock-up of your goal would be useful.

                                 

                                Jonathan

                                • 13. Re: Filtering Using a Hierarchy - I think?
                                  Allan Walker

                                  Hi Matt,

                                   

                                  It's difficult working with hierarchies sometimes.  What I've done in the past is to set up a CF that concatenates results - I'm at a University, so a frequent use is College (of say Art) then Department (e.g. fine art).

                                   

                                  This results in "Artfineart".  You would then have something like ABCapples, ABCoranges, DEFapples, DEFbananas.  Then you can use CONTAINS([CF],"apples") to filter.

                                   

                                  Hope that helps, or at least, gives you an idea.

                                  • 14. Re: Filtering Using a Hierarchy - I think?
                                    Matt Wheelock

                                    Jonathan,

                                     

                                    Again, I apologize.  Let me start from the beginning and explain the context more thoroughly as that may help the understanding.

                                     

                                    I work with 8 sales people. We sell disposable apparel. The product numbers in the example are gloves.  I update a daily invoice report which displays the daily sales orders from the day prior. 

                                     

                                    Each person is assigned a sales number or multiple sales numbers which is/are the values this data is attached to. 

                                    I pull a general sales report from another data base that encompasses the total invoicing for everyone that displays everyone's sales collectively.  Naturally we don't want to display others people's sales data for everyone to see as commissions are involved, etc... 

                                     

                                    Therefore I created Workbook Templates for each sales person that are filtered by each person's respective sales number and the rest of the data that is pertinent to the report. 

                                     

                                    The way go about it is by replacing the data source which is pulled from another data base daily in each sales person's Workbook Template.  For example, Bob's customer is XYZ company.  He has a Tableau report that is generated specifically for him based on his sales numbers and what was invoiced daily.  XYZ company orders 8220-20s and 8220-30s on Tuesday and it shows up on the report.  Wednesday they may order ONLY 8220-20s.  However, his Workbook is custom filtered using the custom filter feature to also capture both 8220-20s and 8220-30s even if the 8220-30s are NOT on the report for that day. 

                                     

                                    Bob also has a shared customer that is assigned to someone else's sales number, confusing I know, and Bob sold 8220-20s and 8220-30s to this customer and the other sales person sold 2330-30s to this same customer.  Bob only gets credit for the 8220-20s/30s and he can only see what was sold for the 8220-20s/30s. 

                                     

                                    The idea is to show Bob the 8220-20/30 invoiced sales for customer XYZ EXCLUDING the 2330-30s.  I can achieve this by manually creating a set and choosing the relevant data daily but this is exactly what I want to avoid-the manual daily step.  I want to create pre-defined parameters that will capture sales and possible sales of the 8220-20/30s as customers don’t necessarily buy the same exact product combinations each time they order.  Monday they may order 8220-20s and 8220-30s.  Tuesday they may order 8220-30s.  Wednesday they may order 8220-20s, etc… 

                                     

                                    I hope this makes sense and I really appreciate all the advice!  

                                              

                                    1 2 Previous Next