5 Replies Latest reply on Jul 22, 2016 8:58 AM by David Li

    Difference Between Two Lines

    Barry Sheldon

      Greetings,

       

      I have a table with multiple columns.  I would like to use a calulated field to graph the difference between two columns which the user selects.

       

      I have created two paramater controls which have a list of the column headings.  I would like to click in these areas and have the chart update to show the different between these two columns.

       

      Any idea how to do this?

       

      Thanks!

        • 1. Re: Difference Between Two Lines
          David Li

          Hi Barry, you'll need to create switches using CASE WHEN to pick the measures and then net them against each other. For instance, something like

           

          CASE [Parameter 1]

               WHEN "Yo" then [Yo]

               WHEN "Goo" then [Goo]

               ...

          END -

          CASE [Parameter 1]

               WHEN "Yo" then [Yo]

               WHEN "Goo" then [Goo]

               ...

          END

           

          Notice the minus sign after the first "END", which is what does the difference calc.

          1 of 1 people found this helpful
          • 2. Re: Difference Between Two Lines
            Barry Sheldon

            Thanks for the quick reply - I have 118 columns - does this mean I'll need to do 118 different cases?

            • 3. Re: Difference Between Two Lines
              David Li

              Unfortunately, if you want to use parameters, then yes, you'll have to do 118 different cases until Tableau puts in something like Excel's INDIRECT() function. You can save yourself some time by building the pieces in Excel. Just put in all your column names in one column in Excel (with quotations around them), then put "WHEN" in the column to the left, "THEN" in the column to the right, and all your column names to the right of that (surrounded by square brackets). Then, you can copy that into Tableau and make final adjustments.

               

              Now, one thing you could potentially do as long as you don't mind reshaping your data is to do a forced unpivot of the data so that you can separate out the measures using a dimension. I'm not 100% sure this will work, but it might be worth a try if you don't want to do what I mentioned above. You can try to reshape the data so that you only have ONE value column. The measures will be differentiated by a new field named "Measure" or something, so that each row of original data will become 118 rows of reshaped data. Then, you can drop that into a sheet, use the Difference table calculation, and use a filter on the new Measure dimension to select them.

              1 of 1 people found this helpful
              • 4. Re: Difference Between Two Lines
                Barry Sheldon

                Ah, okay - thank you for the information!

                • 5. Re: Difference Between Two Lines
                  David Li

                  You're welcome, Barry!