1 2 Previous Next 20 Replies Latest reply on Nov 17, 2017 2:57 PM by Deepak Rai

    How do I use two parts of a pivotted column for x and y values on a graph?

    Laura Shireman

      I'm trying to plot some data (standard curves for analytical chemistry) where the x axis is the mass of a compound I added to a solution, and the y axis is the signal recorded from an instrument (peak height on a mass spectrometer). I'd like Tableau to color code the data by compound (compound A, compound B, compound C, etc.). The original  structure of my data (Public/Standard curve example.csv at master · shirewoman2/Public · GitHub ) was like this:

       SampleID   | Mass A | Mass B | ... | Signal A | Signal B | ... 
      standard 0 |       0|       0| ... |         0|         0| ...
      standard 5 |   2.535|   2.555| ... |     0.494|     1.240| ...
      standard 25|  12.675|  12.775| ... |     2.426|     7.235| ...

      I know how to make graphs one compound at a time with these original data, but for the purposes of other analyses I'm doing with these data and because I want multiple compounds on the same graph, I've pivotted them so that the structure is now like this:

       SampleID   | Compound | Parameter | Value 
      standard 0 | A        | Mass      |     0
      standard 0 | A        | Signal    |     0
      standard 5 | A        | Mass      | 2.535


      How do I make a graph where the mass is on the x axis, the signal is on the y axis, and the points are colored by compound? I don't see a good way to do it when my data are in this format. I've tried making new calculated variables where the value = NULL if the parameter is not equal to "Mass" and another calculated variable where the value = NULL if the parameter is not equal to "Signal" and then putting those pills on the columns and rows, but that's not working. Is there a way to do this in Tableau with data structured like this pivotted form?

      Alternatively, is there a way to spread my pivotted data so that the new structure is like this:

       SampleID   | Compound | Mass  |  Signal
      standard 0 | A        |      0|       0
      standard 5 | A        |  2.535|   0.494
      standard 25| A        | 12.675|   2.426
      standard 0 | B        |      0|       0


      and would that work better?

      (For R users, that last bit would be the equivalent of the tidyr package gather and spread functions.)


      I'm attaching my actual Tableau workbook.

        1 2 Previous Next