6 Replies Latest reply on Aug 20, 2018 11:30 AM by Joe Oppelt

    Combining Columns and Filtering Results in Dashboard?

    Calvin Huff

      I have been trying to work out how if there is a way to take multiple columns from an excel file and combine them into a single dimension. The issue that I have been running into when reading through message boards is that I have three layers (Category > SubCategory > and Item) that each contain multiple columns and that must be able to be filtered by the layer(s) above them.

       

      Initially I thought that I could just pivot all of these columns and use filters to pull out each layer of information. However, after bringing the individual sheets into a Dashboard, I am unable to filter by Category and SubCategory. I assume this is because the pivot function no longer relates to the specific row on which the information was located in the excel file?

       

      I have also attempted to create a parameter to filter the data, but that does not work because I would need a Category and Sub-Category parameter that interact with each other so that once a Category was selected (ie: 'metro') only SubCategories (and by extension Items) that were in 'metro' rows would remain. The Items would then be able to be further filtered by SubCategory. As I understand it, Parameters only function independently of each other.

       

      I have attached a file with dummy data that attempts to recreate the file that I am working with. The actual file is much larger (10,000+ rows) and has has more columns for Category, SubCategory, and Item.

       

      Any suggestions are greatly appreciated. Thanks in advance!

        • 1. Re: Combining Columns and Filtering Results in Dashboard?
          Joe Oppelt

          What does your  data look like before all the pivoting?  If I try to unpivot on a copy of the data source, there are still columns that have "Pivot" in their names.

          • 2. Re: Combining Columns and Filtering Results in Dashboard?
            Calvin Huff

            Joe,

             

            My apologies, I forgot to mention that I duplicated the data in order to work with the data in the pivoted format and in their original format in the same Tableau file. Pivot - [Column Title] was just a way to differentiate the duplicated columns that were used in the Pivot.

             

            I have attached the data file to better illustrate this: The orange headers represent the data's original format, while the blue headers are a duplicate with "Pivot" in the title.

            • 3. Re: Combining Columns and Filtering Results in Dashboard?
              Joe Oppelt

              OK, so looking at that data ... 

               

              Are you saying that for any [Unique ID], there can be up to 3 categories?

               

              For that I would create two columns:  [Category] (with values of 1, 2, 3), and [Category Value] (with the string value shown in the spreadsheet.)  I would have just made a string [Category] dimension, but I see that I can have "metro" for multiple categories under a given [Unique ID], so you'll need to have this 2 column approach to differentiate.  If you want to be able to filter and just see Category3 values, this is the only way I can see you doing that.  (Or are you saying that you want to file all "metro" regardless of which category it falls under?   If so, you would filter by [Category Value] instead.

               

              Now come the part I'm not sure about.  You also have up to 4 SubCategories per Unique ID.  Is there a correlation of some sort between Category and SubCategory?  I an ID has 2 {Categories] and 4 [subCategories], how would they match up?

               

              If there is really not a hierarchy between Category and SubCategory, then instead of calling the proposed column [Category], call it [Criterion], for example.  And shove in the values of Category1, Category2, Category3, SubCategory1, ..., Item5.  Then shove all the individual tokens into the [Criterion Value] field.

               

              I might be totally misunderstanding how you use the data, so these questions are a start in helping me understand.

              • 4. Re: Combining Columns and Filtering Results in Dashboard?
                Joe Oppelt

                One thing about Tableau and PIVOT.  You can only pivot one "chunk" of columns.  You can't pivot, and then over-pivot a second chunk.

                 

                If you use the [Criterion] method I described, you can simply pivot all those columns ( CategoryN, SubCategoryN, ItemN) in one giant pivot.  But if you have to pivot [Categories] and then also pivot [SubCategoties], etc., you'll have to ETL that outside of Tableau.

                • 5. Re: Combining Columns and Filtering Results in Dashboard?
                  Calvin Huff

                  I initially tried to pivot all columns into one pivot, but came across the issue of not being able to filter by a 'Category' and then a 'SubCategory' and then display all Items that were in a row with those values (ie: all rows that contained 'metro' and 'apartment' >> retrieve a count of each 'Item' available ).

                   

                  I am not concerned with whether a value is located in 'Category1', 'Category2', or 'Category3'; my goal is to be able to filter by 'metro' (for example) if it appears in any of these 3 columns. This would then filter 'SubCategory' to only display values that were found in rows where 'metro' was a 'Category'. I would then be able to take the remaining 'SubCategories' (regardless of column 1-4) and select only rows that contain 'house'. This would show a final list of 'Item's that appear in rows with 'metro' and 'house'.

                   

                  To answer your question of 'If an ID has 2 {Categories] and 4 [subCategories], how would they match up?' : I would want to display all 4 [SubCategories] under both/either 2 [Categories]. In other words, if 'metro' and 'rural' are shown as a 'Category', I would want to display all 4 [SubCategory(s)] (ie: apartment, farm, house, and trailer) under both 'metro' and 'rural' when filtered. I realize this seems counterintuitive, but it makes more sense in the original data file.

                   

                  I think your suggestion of two columns: [Category] (with values of 1,2,3) and [Category Value] (with the string value shown in the spreadsheet) is on the right track with what I'm looking for, but the string in [Category Value] would not allow a user to select a specific 'Category' (ie: 'suburbmetrorural' when I would want to indicate just 'rural').

                   

                  I realized that this may be outside of Tableau's ability with the way the data file is set up currently, but since I am still new to Tableau, I wanted validation before I started trying to reformat the data file, given its size.

                  • 6. Re: Combining Columns and Filtering Results in Dashboard?
                    Joe Oppelt

                    To be able to filter by Category and then by subcategory implies that there is a relationship between the two classifications.


                    But from what I see in your example data, there can't be a relationship.

                     

                    To filter by Category would mean, "get me all the rows with x-Category".  And then a subsequent filtering by sub-category would mean, "... and then from among those with x-Category, give me all the rows that have y-SubCategory.

                     

                    The problem is that your data is not normalized.

                     

                    You're not going to make this work with filters, anyway.  You might be able to hack up a machine of parameters that will let the user specify one of the category designations (1, 2, 3) and then choose a valid [Category Value] in that category, and that will grab you all the IDs that satisfy the request, and also specify a sub-Category designation (1-through-4), and a [SubCategory Value].  That would be a minimum of 4 params.

                     

                    And if you want them to be able to pick MULTIPLE Categories and subCategories, it would require multiple params to handle that.  (Ditto multiple values.)

                     

                    I can envision it.  I don't want to be the one to design it or maintain it though.