4 Replies Latest reply on Feb 18, 2019 2:47 AM by Marco Doncel

    Aggregate data by all possible combinations of table

    Marco Doncel

      I have data about packages movement within our logistics centers. We have the following tables:

       

      PACKAGES

      Reference for each package plus several info fields.

      Package ReferenceAddressWeight
      1xxxxxx2
      2xxxxxx4
      3xxxxxx5
      4xxxxxx2
      5xxxxxx1

       

       

      TRANSIT

      Each logistics center the packages have been in.

      Logistics center
      Datepackage reference
      Madridxxxxxx1
      Parisxxxxxx1

      Madrid

      xxxxxx2
      Barcelonaxxxxxx1
      Romexxxxxx1
      Lyonxxxxxxx2
      Lyonxxxxxxx3
      Barcelonaxxxxxxx2
      Milanxxxxxxx3
      Romexxxxxxx3
      Madridxxxxxxx4
      Parisxxxxxxx5
      Barcelonaxxxxxxx5

       

      LOGISTICS CENTERS

       

      Logistics centercountry
      MadridSpain
      BarcelonaSpain
      MilanItaly
      RomeItaly
      ParisFrance
      LyonFrance

       

      I would like to create a table with Tableau Desktop where I can have all the packages for each combination of countries like the following:

      CombinationTotal Packages
      Spain1
      Italy0
      France0
      Spain, Italy0
      Spain, France2
      Italy, France1
      Spain, Italy, France1

       

      Is it possible to build this kind of table dynamically?

        • 1. Re: Aggregate data by all possible combinations of table
          Esther Aller

          Hey Marco,

           

          Maybe you can explain a little more about how that final table is created? I don't understand how "Spain" has 1 total package and "Italy" has zero, but "Spain, Italy" has zero. Shouldn't "Spain, Italy" have 1 total package?

          • 2. Re: Aggregate data by all possible combinations of table
            Marco Doncel

            Thanks for the reply Esther Aller

             

            The idea behind that final table is to have each package categorized under the different countries where they've been. The tag "Spain", for instance, means that the package has only been in Spain, the tag "Spain,Italy" means that the package has only been exactly in those two countries... and so on. For that reason, if the package belongs to "Spain,Italy" it can't also belong to "Spain" or "Italy". Summarizing, each package will sum to exactly one of the categories.

             

            Hope this clarifies things a bit.

            • 3. Re: Aggregate data by all possible combinations of table
              Esther Aller

              Hi Marco,

               

              So Creating a String List to Display in a Tooltip is a way to dynamically create the list of countries. However the resulting country lists cannot be used to partition the data. Or in other words, if you use table calculations to create the country lists then you will be unable to count up the number of packages per country list.

               

              Instead you need the country list to be a dimension, which can be accomplished with FIXED (See Level of Detail Expressions - Tableau ). I have attached a workbook with the following calculation:

               

              { FIXED [package ID] : MAX(IF [Country] = "France" THEN "France" ELSE "" END )}

               

              + IF { FIXED [package ID] : COUNTD([Country])} >1 THEN ", " ELSE "" END

               

              + { FIXED [package ID] : MAX(IF [Country] = "Italy" THEN "Italy" ELSE "" END )}

               

              + IF { FIXED [package ID] : COUNTD([Country])} >2 THEN ", " ELSE "" END

               

              + { FIXED [package ID] : MAX(IF [Country] = "Spain" THEN "Spain" ELSE "" END )}

               

              The green expressions are adding a comma if there is another country. The blue lines will add the name of the country if the package went through it. A new blue line will have to be added for every country. This will dynamically determine what all of the country groupings are, but it will not list the zeros you have in your final chart.

               

              Hope this helps

              • 4. Re: Aggregate data by all possible combinations of table
                Marco Doncel

                Great approach Esther Aller . The Only issue I see is when, for instance, a package has been to two countries different to France. Let's say we add a new package:

                Center          Package ID

                Rome           6

                Barcelona    6

                 

                Would give us the following output:

                Screenshot 2019-02-18 at 11.19.37.png

                 

                As it is only a visual tag it doesn't matter if it is "France,Italy,Spain" or "France Italy Spain" so removing all the "," calculations and adding a space at the end of each country would be enough:

                 

                { FIXED [package ID] : MAX(IF [Country] = "France" THEN "France " ELSE "" END )}

                 

                + { FIXED [package ID] : MAX(IF [Country] = "Italy" THEN "Italy " ELSE "" END )}

                 

                + { FIXED [package ID] : MAX(IF [Country] = "Spain" THEN "Spain " ELSE "" END )}

                 

                The result would look like this:

                 

                Screenshot 2019-02-18 at 11.45.14.png