4 Replies Latest reply on Aug 22, 2018 4:22 AM by Josh Bain

    Comparing Rows in same Column

    Josh Bain

      Hi All,

       

      I have a table which has a bunch of demographics in one column, their banding in another, and their value in a third column.

       

      I would like to be able to allow a user to compare one another, i.e. Age_Band versus Country. Ideally their banding would be shown as a chart and the value as a % of the total.

       

      I have tried creating parameters, filters, calculations to no prevail. All that seems to end up happening is that I can compare e.g. Age_Band to Age_Band but the view blanks out when I try compare Age_Band to Country etc.

       

      Any help would be appreciated. At this time I am unable to attach a workbook with dummy data, but I've included some random sample data below to highlight how the data is structured.

       

      Thanks,

      Josh

       

      Demo 1 SelectionDemo 2 SelectionValue
      Age_Band16-2420,000
      Age_Band85+63,000
      CountryWales764,000
      Age_Band25-34581,000
      CountryScotland123,000
      Age_Band75-84258,000
      Age_Band35-44138,000
      Age_Band65-74241,000
      Age_Band45-54879,000
      Age_Band55-64783,000
      GenderMale1,192,000
      GenderFemale1,794,000
      CountryEngland2,389,000
        • 1. Re: Comparing Rows in same Column
          Ankit Patel

          Hi Josh,

           

          I think the issue is gaps in Data - Age-Band does not associate with Country or Gender - same way Country doesn't tie back to Age-Band. If you create calculations based on 1st column (see below calcs) and put a view together with 2nd column (see below image) you can see there is no co-relation.

           

          When looking a % of Total - you get below breakdown. Hope this helps.

           

          Age-Band Value

          if [Demo 1 Selection] = 'Age_Band' then [Value] else 0 end

           

          Country Value

          if [Demo 1 Selection] = 'Country' then [Value] else 0 end

           

          Gender Value

          if [Demo 1 Selection] = 'Gender' then [Value] else 0 end

           

             

          2 of 2 people found this helpful
          • 2. Re: Comparing Rows in same Column
            Andy Paddle

            Wondering if there is any other way to solve this - I have same issue - any help would be great.

            • 3. Re: Comparing Rows in same Column
              swaroop.gantela

              Josh,

               

              I didn't quite catch the gist, but wanted to toss out and idea and see if it would be helpful.

               

              This uses the method of unioning the data to itself:

              Example of Water Fall Charts - 2 - Still Struggling with Excel ?? <Tableau's Room>

              This may not be available for all data types, but you could probably accomplishment

              it in Prep.

               

              The idea is that the self-union creates a new dimension called Table Name which

              you can then use in calculations.

               

              If you are only comparing one demo to another, you can make two parameters

              and then selectively filter the CopyA version by parameterA, and then the CopyB

              version by parameter. If needed, you could fix the selected value to the entire

              set so that you could do a direct comparison between the two selections.

               

              If you want the user to select multiple demos, you can create a concatenated

              field of Table Name + Demo 1 Selection and filter on that.

               

              Not sure if this is at all pertinent to your problem, but if it is, it just a

              very preliminary step. May not be fully functional for your final graph.

              But we can explore that further.

               

              Please see workbook v10.3 attached in the Forum Thread.

              Dashboard1 uses parameters, the multiselect sheet uses a filter.

              • 4. Re: Comparing Rows in same Column
                Josh Bain

                Thanks everyone for taking the time to contribute to this post.

                 

                For the purposes of my analysis I have taken Ankit's answer on board and have gone back to my data and changed the SQL to include more aggregations. It had been simplified too much for the last piece in my dashboard puzzle.

                 

                Hopefully swaroop.gantela's answer will come in handy for Andy.

                 

                Cheers,

                Josh