4 Replies Latest reply on Nov 17, 2011 9:55 PM by Joseph Ethan

    Can Tableau do Simple Modeling?

    Joseph Ethan

      Hi Everyone,

       

      They are many scenarios where it would be very useful to utilize a model to find others which share the same characteristics.

       

      I have put together a simple theoretical dataset of all members, all clubs and the mapping of members to clubs (3 tables). I have also attached a packaged workbook that everything except the hoped-for filtering.

       

      Please see the screenshots of before and after (desired) along with a packaged workbook. The raw sample data is also provided in an Excel doc for your perusal.

       

      The only condition is that you must use the parameter control ("member_name") to drive any resulting filter calculation.

       

      Any insight would be greatly appreciated.

       

      -Joe

        • 1. Re: Can Tableau do Simple Modeling?
          Joe Mako

          How about the attached?

           

          Just a slight modification of your Color Identifier field, and then set the Compute using for this new field to be member_name, and ctrl-drag-and-dropped onto the filter shelf, keeping only when true, and then set the Color Identifier pill that is on the Color shelf to the Attribute aggregation.

          • 2. Re: Can Tableau do Simple Modeling?
            Joseph Ethan

            That's pretty amazing and exactly what I wanted! I just wish I understood the principle so I could apply it generally. About me, I'm an average user, but I 'm enthusiastic. Is there anything you say in a couple sentences about the theory behind your solution? Any other posts that elaborate on your method? Most of all, thank you!

            • 3. Re: Can Tableau do Simple Modeling?
              Richard Leeke

              Let me break down Joe's extra calculated field for you.  The entire calculation is this:

               

              TOTAL(SUM(IIF([Choose a Member Name to Find His Co-Members]=[member_nick_name],1,0)))>0

               

              Breaking that down, the inner expression:

               

              IIF([Choose a Member Name to Find His Co-Members]=[member_nick_name],1,0)

               

              returns 1 if the member is the chosen one and 0 otherwise.

               

              The:

               

              SUM()

               

              on it's own would say add up the value of the previous expression over all underlying rows in the database for each row in the view in turn.  In this case there is probably only one database row for each view row, so this will again just return 1 or 0, but the aggregate is needed for the next function:

               

              TOTAL()

               

              is a "table calculation" function which says evaluate the enclosed aggregate expression (i.e the SUM() in this case) over all rows in the "partition", rather than for each row in turn.  Table calculations are quite a difficult topic to explain succinctly, but in this case, with the "Compute Using" set to [member_name], it means calculate the total SUM() over all members of each club.  So for clubs which have the chosen person as a member this will evaluate to 1 for each member of the club (because in total there is one member with that name) and for the the other clubs it will evaluate to 0 for each member (because there are no members with that name).

               

              Finally, Joe tacked this on the end:

               

              >0

               

              That turns the whole expression into a comparison which tests to see if (the total number of members of the current row's club with the chosen name) is greater than zero.  So the overall expression returns a true/false value: true for clubs which include the chosen member, false for other clubs.

               

              That expression can be dropped on Level of Detail (at which point the Compute using can be set) and then copied onto the filter shelf.

               

              Hopefully that helps.

              • 4. Re: Can Tableau do Simple Modeling?
                Joseph Ethan

                Thank you Richard. I think I understand, but it's really about the journey more than the destination meaning I don't mind being a student of you two. Thanks again Joe! I love what Tableau can allow us to do.

                 

                -Joseph