(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.
Community B.twbx 55.0 KB
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".
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
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.
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.
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!
Community (3).twbx 26.3 KB
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
1 of 1 people found this helpful
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.
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?