11 Replies Latest reply on Dec 3, 2013 11:26 AM by Nicolas Meakins

    Need help filtering between a set of groups and vendors!!!

    . Jasonz

      Hi Folks,

       

      Could really use some help here!

       

      Attached is a text.xlsx with some dummy data.

       

      What I need to do is separate out the following:

       

      Group A

       

      Group B + Vendor A

       

      So:

       

      Group A = 3 (tickets 123,1234,12345)

      Group B = 3 (tickets 1234567,12345678,12345678910)

       

       

      Cant seem to figure this one out and I know im missing something simple.

        • 1. Re: Need help filtering between a set of groups and vendors!!!
          Matt Lutton

          So, what is your question in relation to Tableau?   Are you asking for calculations that will separate these out?

           

          If each grouping should be unique, then something like the following should get you close (you could also write one calc for each group, if that makes more sense).

           

          IF [Group]='group a' and [Vendor]='' then 'Group A' // this assumes Tableau interprets the blank cell as an empty string

          elseif [Group]='group b' and [Vendor]='' then 'Group B'  //same assumption here

          elseif [Group]='group a' and [Vendor]='vendor A' then 'Group A + Vendor A'

          elseif [Group]='group b' and [Vendor]='vendor A' then 'Group B + Vendor A'

          // if you want an ELSE condition, you can add it here.

          END

          //You can write more of these if needed for more scenarios in your data but in your sample data the above scenarios are all I found

           

          Note that '' is two single quotes or an empty string, not double quotes


          Let me know if this is not what you are looking for.  I wasn't sure based on your text description what you are trying to accomplish--you are always better off bringing your data inside Tableau, and posting a packaged workbook on the forum, as well as a detailed description of what you are trying to accomplish and what you've already done on your own in an attempt to get there--AND a clear description of the end result you are looking for (or a mock up).

          • 2. Re: Re: Need help filtering between a set of groups and vendors!!!
            . Jasonz

            Hi Matthew,

             

            see attached, what I want to see is:

             

            group A :  123

                             1234

                             12345

            Group B: 1234567

                            12345678

                            12345678910

             

            Tried your idea but it didnt group as expected.

            • 3. Re: Re: Need help filtering between a set of groups and vendors!!!
              Matt Lutton

              I don't see an attachment; Will check back again tomorrow if you do upload a workbook.  Thanks

              • 4. Re: Need help filtering between a set of groups and vendors!!!
                Nicolas Meakins

                Hi Jason,

                 

                I had a quick look at your question and excel file, but I don't quite understand how you expect to get the result you want with that data:

                 

                Tickets 123, 1234 and 12345 all are exclusively in Group A.

                Ticket 1234567 has two combinations; one is group a and the other is group a + Vendor A.

                Ticket 12345678 has 3 combinations; group a, group b + vendor A, group b

                Ticket 12345678910 has 2 combinations; group a, group b

                 

                Essentially you have nothing that identifies the 3 tickets that you want in Group B as different from the others in every instance. So using your rules you would see the following:

                 

                ticketGroupVendorResult
                123group a-Group A
                1234group a-Group A
                12345group a-Group A
                1234567group avendor AGroup B
                12345678group a-Group A
                12345678910group a-Group A
                123group a-Group A
                1234group a-Group A
                12345group a-Group A
                1234567group a-Group A
                12345678group bvendor AGroup B
                12345678910group b-Group B
                123group a-Group A
                1234group a-Group A
                12345group a-Group A
                1234567group a-Group A
                12345678group b-Group B
                12345678910group b-Group B

                 

                and your final view would be:

                 

                group A :     123

                                     1234

                                     12345

                                     1234567

                                     12345678

                                     12345678910

                Group B:     1234567

                                     12345678

                                     12345678910

                 

                Does this make sense?

                • 5. Re: Need help filtering between a set of groups and vendors!!!
                  Matt Lutton

                  I didn't bother to look at the data in the Excel file closely, so thank you Nicolas - this is a good example of how (for me) a packaged workbook really helps users help each other much more readily in Tableau. 

                  • 6. Re: Re: Need help filtering between a set of groups and vendors!!!
                    . Jasonz

                    hopefully the attachment works this time!

                     

                    I changed a bit of it around so I could explain myself better:

                     

                    Assume all the tickets are owned by one group (in this case "blue")

                    tickets are assigned to different groups or vendors as needed

                    I want to separate the data  (tickets) into 2 sections: one with just group A only (no vendors, no other groups assigned) and the second everything else...

                     

                    Assume a life cycle of : ticket is opened and assigned, it also maybe assigned to other groups for work or vendors as needed. so a ticket may have multiple assignment groups or vendors.

                     

                     

                     

                    example :

                    ticket # 12345678910

                    owner: blue

                    assigned group: group A

                    vendor:

                    assigned group: group B

                     

                    *this ticket had 2 assigned groups to it.*

                     

                    and

                     

                    ticket: 123

                    owner : blue

                    first assigned group : group A

                    vendor:

                     

                     

                    I hope this helps, I could keep adding more fields but im hoping this makes sense.


                    • 8. Re: Need help filtering between a set of groups and vendors!!!
                      Nicolas Meakins

                      OK Jason, I think I understand what you want. Essentially if there are no instances of a ticket being in multiple groups or vendors then it would fall under Group A, otherwise it would be categorized as group B. See attached for my solution:

                       

                      Let me know if this was what you were looking for..

                       

                      Cheers, Nic

                      • 10. Re: Re: Need help filtering between a set of groups and vendors!!!
                        . Jasonz

                        here is another level added (see attached):

                         


                        say there is a sub group (in this case 1,2 or 3) and you wanted to separate group A from group B further by saying anything with sub group greater than 1 is group B

                         

                        the results should only show group A being 123 and the rest under group B

                        • 11. Re: Re: Re: Need help filtering between a set of groups and vendors!!!
                          Nicolas Meakins

                          You can just add another OR test in your Group Test: or max([sub group])>1

                           

                          See attached: