2 Replies Latest reply on Oct 1, 2012 6:39 AM by David Edwards

    Can one row be included in multiple marks?

    David Edwards

      Okay this is my first posted question, so hopefully I can give the right amount of background information.

       

      The basic question is:

        Can a single row of data be included in more than one mark?

        If so, how?

       

      Some Background...

       

      My Data:

        The data file represents rating of a work product. One row represents the rating of a single instance of the product.

       

        The product is first rated by two 'Novices' (Rater 1 and Rater 2)

        The order of rating is forcibly preserved in the column headings.

       

        The product is then rated an 'Expert' who provides two scores. These are averaged to form a baseline against which accuracy is measured

       

      The goal:

        Measure the accuracy of the novice raters against the expert baseline, and provide the information in a bar graph

        All rater's accuracy must be displayed simultaneously

        The accuracy calculation for a given novice should include all the data whether they participated as the first or second rater

       

      Data Picture.png

           Basically I want row 2 to contribute to the mark representing both Rater C and Rater H, but conditionally use the correct score column in the accuracy calculation.

       

      The Workbook:

        Attached is a workbook where I took an approach using a parameter and several calculated fields to perform the heavy lifting.

        The trouble is that the workbook culminates in a dashboard that selects a single rater and displays detailed information on the dashboard. The dashboard works great for individuals, but it doesn't provide any good summary capabilities.

       

        I've provided two demo worksheets which point in the direction of where I'm trying to get

                Group Accuracy (parameter)

                Group Accuracy (no parameter)

        Each has it's own problems which are described in the captions

       

      The Rules:

      I would prefer not to reshape my data before bringing it in for a few different reasons, so I'm looking for a solution which does not require this.

      Using Custom SQL to alter the shape of the data as it is pulled into Tableau from the excel file might be an option, however.

       

       

      Thanks!

      ~Dave

        • 1. Re: Can one row be included in multiple marks?
          Alex Kerin

          I think in the absence of a reshape then custom SQL is your best option. I think this is pretty straightforward by reworking the existing SQL code to something like this (see second option below which is probably better):

           

          SELECT [DataExport$].[Arg AES Flag] AS [Arg AES Flag],

            [DataExport$].[Arg ES Flag] AS [Arg ES Flag],

            [DataExport$].[Arg Rater1 Id] AS [Arg Rater ID],

            [DataExport$].[Arg Rater1 Score] AS [Arg Rater Score],

            [DataExport$].[Candidate Type] AS [Candidate Type],

            [DataExport$].[File Date] AS [File Date],

            [DataExport$].[Registration Id] AS [Registration Id],

            1 AS [Rater Order]

          FROM [DataExport$]

           

           

          Union all

           

           

          SELECT [DataExport$].[Arg AES Flag] AS [Arg AES Flag],

            [DataExport$].[Arg ES Flag] AS [Arg ES Flag],

            [DataExport$].[Arg Rater2 Id] AS [Arg Rater ID],

            [DataExport$].[Arg Rater2 Score] AS [Arg Rater Score],

            [DataExport$].[Candidate Type] AS [Candidate Type],

            [DataExport$].[File Date] AS [File Date],

            [DataExport$].[Registration Id] AS [Registration Id],

            2 AS [Rater Order]

          FROM [DataExport$]

           

           

          union all

           

           

          SELECT [DataExport$].[Arg AES Flag] AS [Arg AES Flag],

            [DataExport$].[Arg ES Flag] AS [Arg ES Flag],

            [DataExport$].[Arg Rater3 Id] AS [Arg Rater ID],

            [DataExport$].[Arg Rater3 Score] AS [Arg Rater Score],

            [DataExport$].[Candidate Type] AS [Candidate Type],

            [DataExport$].[File Date] AS [File Date],

            [DataExport$].[Registration Id] AS [Registration Id],

            3 AS [Rater Order]

          FROM [DataExport$]

           

           

          union all

           

           

          SELECT [DataExport$].[Arg AES Flag] AS [Arg AES Flag],

            [DataExport$].[Arg ES Flag] AS [Arg ES Flag],

            [DataExport$].[Arg Rater4 Id] AS [Arg Rater ID],

            [DataExport$].[Arg Rater4 Score] AS [Arg Rater Score],

            [DataExport$].[Candidate Type] AS [Candidate Type],

            [DataExport$].[File Date] AS [File Date],

            [DataExport$].[Registration Id] AS [Registration Id],

            4 AS [Rater Order]

          FROM [DataExport$]

           

          This pulls each ID into 4 rows - now this may not be perfect as you've renamed the Arg Rater4 Score as the second expert score and to do easy rater to expert comparisons we will end up with lookups.

           

          This may be better:

           

          SELECT [DataExport$].[Arg AES Flag] AS [Arg AES Flag],

            [DataExport$].[Arg ES Flag] AS [Arg ES Flag],

            [DataExport$].[Arg Rater1 Id] AS [Arg Rater ID],

            [DataExport$].[Arg Rater1 Score] AS [Arg Rater Score],

            [DataExport$].[Arg Rater3 Id] AS [Expert Arg Rater ID],

            [DataExport$].[Arg Rater3 Score] AS [Expert Arg Rater Score 1],

            [DataExport$].[Arg Rater4 Score] AS [Expert Arg Rater Score 2],

            [DataExport$].[Candidate Type] AS [Candidate Type],

            [DataExport$].[File Date] AS [File Date],

            [DataExport$].[Registration Id] AS [Registration Id],

            1 AS [Rater Order]

          FROM [DataExport$]

           

           

          Union all

           

           

          SELECT [DataExport$].[Arg AES Flag] AS [Arg AES Flag],

            [DataExport$].[Arg ES Flag] AS [Arg ES Flag],

            [DataExport$].[Arg Rater2 Id] AS [Arg Rater ID],

            [DataExport$].[Arg Rater2 Score] AS [Arg Rater Score],

            [DataExport$].[Arg Rater3 Id] AS [Expert Arg Rater ID],

            [DataExport$].[Arg Rater3 Score] AS [Expert Arg Rater Score 1],

            [DataExport$].[Arg Rater4 Score] AS [Expert Arg Rater Score 2],

            [DataExport$].[Candidate Type] AS [Candidate Type],

            [DataExport$].[File Date] AS [File Date],

            [DataExport$].[Registration Id] AS [Registration Id],

            2 AS [Rater Order]

          FROM [DataExport$]

           

          We only have double the data now with each rater on a separate row with the expert scores duplicated for both. The only caveat is making sure this duplication does not cause any issues with sums or averages you may create (actually, averages are probably okay...)

          1 of 1 people found this helpful
          • 2. Re: Can one row be included in multiple marks?
            David Edwards

            Alex,

              I have tried this Custom SQL as you presented it, and it works like a charm.

             

              I can't help but also believe that this solution gets around the original question, because it actually creates two rows out of every one row in the original dataset.

             

              Admittedly this is the soluton that I used when I solved the problem using pure SQL before I had Tableau. I just couldn't quite get past the extra symbols in the syntax ($, [ and ]) to quite figure it out.

             

            Thanks!