8 Replies Latest reply on Mar 6, 2017 5:48 PM by Lisa Connelly

    Aggregating Sets - best options?

    Lisa Connelly

      Hi-

       

      I'm a total Tableau newbie and not a statistician so bear with me. My company has a number of retail locations around the country. We offer multiple program types to customers of those locations. I am trying to get a count of specific program customers by each retail location.

       

      I have started out by creating SETS of each program type.

      Then I created a calculated field to COUNT the number of customers in each of those SETS.

      Then I place them on my canvas where I have already added the retail location in rows.

       

      The result I receive is not what I expect. I expect to see varying numbers of customers within each SET per location. However, I'm seeing the same number of customers per SET for each location. I feel like there is a simple solution to this, but I can't see it. Am I using the wrong calculation function? Any help would be much appreciated...

        • 1. Re: Aggregating Sets - best options?
          Patrick A Van Der Hyde

          Lisa,

           

          What happens when Location is placed on Rows and Program is placed on Columns with a Count(customer id) in the view?  I have attached a Superstore sample view of something like this.  Just wondering whty this does not work?  If there is something about the data throwing off the expected results?

           

          The example is done in Tableau 10.2. 

           

           

          If you have a bit of sample data to show the issues that you are running into or worse case, a picture.  That would help a lot. 

           

          Patrick 

          • 2. Re: Aggregating Sets - best options?
            Jim Dehner

            Hi Lisa - And hi Patrick -

            I have read your problem statement a couple of times and have some questions - You created Sets of programs - I'm a little confused - what gets grouped together to form a Set - and are you placing the Sets or Programs on your column shelf to form a grid? are the Sets unique - no overlapping element. Can a customer purchase from more than one program (set) and can they shop at different locations?

             

            Jim

            • 3. Re: Aggregating Sets - best options?
              Lisa Connelly

              Hi Jim and Patrick -

               

              Thank you for your responses. You helped me realize that I am missing another part of the equation.

               

              Each program type is actually captured as part of a visit record. For example, let's say we have 3 program types called Apples, Bananas and Pears. The data set might look something like this across multiple locations:

               

              LOCATION 1:

              Customer A

              Visit #1: Apples

              Visit #2: Apples

              Visit #3: Bananas

               

              Customer B

              Visit #1: Apples

              Visit #2: Bananas

              Visit #3: Bananas

               

              Customer C

              Visit #1: Apples

              Visit #2: Apples

              Visit #3: Pears

               

               

              LOCATION 2:

              Customer A

              Visit #1: Bananas

              Visit #2: Bananas

              Visit #3: Bananas

               

              Customer B

              Visit #1: Pears

              Visit #2: Apples

              Visit #3: Apples

               

              Customer C

              Visit #1: Apples

              Visit #2: Bananas

              Visit #3: Bananas

               

               

              So what I really need is the count of visit records with a specific program type, distributed by location. That sounds like a couple of calculated fields to get there?

              • 4. Re: Aggregating Sets - best options?
                Jim Dehner

                good - Yes that is getting closer to the answer -

                 

                If I have this straight each location is the only unique part - customer (visits) are multiple and each customer can buy multiple programs within a visit

                So the business question is location dependent and you have 3 other variables - customers - visits - programs

                Not certain what you are trying to show but you can think about LOD expressions to isolate Locations or looking from the program perspective to identify which locations were the most successful -

                 

                OK you are on a good path

                 

                Jim

                • 5. Re: Aggregating Sets - best options?
                  Lisa Connelly

                  Thanks Jim. What I'm trying to get to is something like this:

                   

                   

                                           # Apples          #Bananas       #Pears

                  Location A

                  Location B

                  Location C

                  ...

                   

                  So the # of Apples, Bananas, and Pears are really counts of Visits (can only have one program per visit), so it seems I can't just use Sets as my guide.

                   

                  I'm going to have to learn more about the LOD Expressions I suppose. Easy for a person who is not a statistician to understand? Or would you recommend seeking some expertise?

                   

                  Thank you!

                  • 6. Re: Aggregating Sets - best options?
                    Jim Dehner

                    Hi -

                     

                    I'm no Statistician so don't let me lead you astray -

                     

                    I would suggest a little learning experiment

                    set up the vis just like you have above with Programs (apples etc) for columns and location for row then just put in Visits and get the sum of the visits

                     

                    Then try something like {Fixed [location]: sum([visits] )}

                    Then try to do some calculation to determine the % of the totals that is represented by each cell and see hoe the LOD expression can play into that calculation

                     

                    You might also want 1 more {fixed:sum(visits)}

                     

                    I am an old excel guy and I needed to do something like that to understand how to get to the answers I wanted

                     

                    Let me know how it goes

                    Jim

                    1 of 1 people found this helpful
                    • 7. Re: Aggregating Sets - best options?
                      Jim Dehner

                      After you create your grid and lod calculations play with time filters and

                      see what effect they have

                      Enjoy

                      Jim

                       

                      On Mar 6, 2017 5:39 PM, "Lisa Connelly" <tableaucommunity@tableau.com>

                      • 8. Re: Aggregating Sets - best options?
                        Lisa Connelly

                        I'll give it a try! Thank you!