5 Replies Latest reply on Dec 28, 2016 7:56 AM by Joe Oppelt

    Design and Actual Process Parameter s in Same view

    Sai Manikanta N

      Hi,

       

      I have design curve and actual values in two different tables. I want plot design (Load Vs Parameter) as Line and Actual Process Parameters as dots or shapes in same view. Show how much  actual value is deviated from Design value for particular load

       

      Like

       

      Capture.JPGCapture.JPG Capture.JPG

       

      I'm attaching sample dummy data but in real we have huge records.

       

      Jonathan DrummeyGuy CuthbertPrashant SharmaPooja GandhiAndy Kriebel

      Hrvoje Gabelica

       

       

      Best Regards,

      Sai

        • 1. Re: Design and Actual Process Parameter s in Same view
          Joe Oppelt

          (Note to self:  V 10.1 workbook here.)

           

          See Sheet 4 in the attached.

           

          If you're going to use data from two different data sources, you'll need some sort of common field to blend on.

           

          In the attached, I took your "Design" Data and changed it to make [Report ID] a common field between the two sources.  (You'll need to figure out the right data relationship here.  I just hacked A1 to be equivalent to ID = 1 in the "Design" data source, and A2 to match with ID = 2, etc.)

           

          Now we can put two measures on a sheet, and make them "Dual Axis".  This lets me format the two measures differently.

           

          I changed [Load] from measures to dimension.  (To run one of the dual axis measures as a line, you have to be running along a dimension.  If you run along a measure, then dual axis will only put dots on your sheet.  Rebuild what I did here, but without changing [Load] to Dimension, and you'll see what I mean.)

           

          Given that this is a very simplified example of your actual workbook, we may find that what I did here doesn't translate well to your actual workbook.

           

          Note that I did not take into account the value of [Load] from Sheet2.  (That's why the red dots are in different positions on my Sheet 4.)  This may become a problem if you need to do that.

          • 2. Re: Design and Actual Process Parameter s in Same view
            Bobby Souza

            Hi Sai,

             

            See attached for workbook.

             

            Since the Id and Report Id field values are not similar (1 vs A1, 2 vs A2, etc.) and one is labeled as a Number and another as String the Union did not recognize these ID's as fields that we can merge together. Note: Depending on where this data is stored, you may want to modify one of the ID fields to show similar aliases so that Tableau can more easily recognize these fields as those you'd like to merge during a union.

             

            Step 1: Edit the "Union-ed" data source and highlight both the Id and Report Id fields. Then, select Merge Mismatched Fields --> This will create a new field labeled "Id & Report Id".

            Merge Mismatched Fields.png

             

            2) Go back to sheet and create 4 calculated fields --> this will help us distinguish between our Actual Loads and Values versus Design Loads and Values.

            • Label these Actual Load & Actual Value

                      [IF LEFT([Id & Report Id],1)="A" THEN [Load] ELSE NULL END

                      IF LEFT([Id & Report Id],1)="A" THEN [Value] ELSE NULL END

            • Label these Design Load & Design Value

                      IF LEFT([Id & Report Id],1)="A" THEN NULL ELSE [Load] END

                      IF LEFT([Id & Report Id],1)="A" THEN NULL ELSE [Value] END

            CalculatedFields.png

             

            3) Bring your Design Load & Value as well as your Actual Load and Value pills into the Columns and Rows, respectively. Right click on Actual Load and select Dual Axis (do the same for Actual Value). Then, select Synchronize Axis for both of the Actual axis as well. This will help merge the Design and Actual measures into one chart.

            Dual Axis, Sync.png

             

            4) Now, go up to Analysis and select the Aggregated Measures to remove the aggregations and show all distinct measures values (then we don't have to drag ID in to the details mark). Then, select your Design mark and switch to "Line" and then select your Actual mark and switch to circle.

             

            Circle.png

             

            Note: I understand why you're trying to create a visual of line versus dot plot but since the chart is not displaying time why not choose to compare design versus actual using shapes and colors. See sheet 5 to see what I mean. This makes it much easier as you don't have to create 4 calculated fields --> you just need to create one calculated field that separates Actual versus Design (see Calculation 1) and drag and drop that into shapes and/or colors.

             

             

             

             

            Hope this helps!

            • 3. Re: Design and Actual Process Parameter s in Same view
              Sai Manikanta N

              Hi Joe & Bobby,

               

              In my case I have test id in design value table and date as dimension in actual value table. Means ever day I have different load and actual value.

               

              I can not find common relationship except equipment id

               

              How should I proceed with this data

               

              Best Regards,

              Sai Manikanta

              • 4. Re: Design and Actual Process Parameter s in Same view
                Bobby Souza

                Morning Sai,

                 

                I know you showed the sample in Excel but what data source are you pulling from? Is this stored in a SQL database?

                 

                I would either adjust the query to manipulate the field name or just merge the mismatched fields in Tableau one time (see step 1 from my previous post) - every time you refresh your extract Tableau should recognize that those two ID fields will be merged together. All you're looking for is to combine the two ID fields (regardless of how different they are) into one field when you union.

                 

                If this data is stored in some type of database, we may have more flexibility to manipulate the sources prior to bringing into Tableau.

                1 of 1 people found this helpful
                • 5. Re: Design and Actual Process Parameter s in Same view
                  Joe Oppelt

                  Sai -- There has to be SOME sort of relationship if you want to tell Tableau to place one chunk of data on a sheet in a position relative to some other chunk of data.

                   

                  Does [Equipment ID] give you enough common relationship to tell Tableau to place one dot near another?