Blend the two data sources on the common field.
Then you can just drag the measure from the secondary source onto your Measures shelf. It will have to be used as an aggregate (such as SUM or MIN or COUNT, etc.) Tableau does the work for you.
You didn't attach any files or I would have hacked up a simple example for you. Can you attach two sample file (and you can replace any proprietary data with bogus values.) I don't need all you data. Just a few rows from a few examples of the key field that is the same in both files.
And what version of Tableau are you using. I'll upload an example that you'll be able to read if I know your version.
PS: There is no limit (that I know of) to the number of measures you can drag into the Measures shelf. It's certainly not 4 if there is a limit!
Wow thanks for the quick response!
Here's a sample excel file with 2 worksheet.
So between those sheets, the Activity ID column are equal. So what I'm hoping for in a table is hopefully like this:
Activity Count - this can be the total number of activity IDs between the 2 sources.
Successor Count - is the number of successors between the two. Same as predecessors and constraints.
I can't seem to figure out how to combine all those aggregates into one single table in a worksheet.
Thanks in advance!
P6 Sample Date - Copy.xlsx 1.8 MB
I saved this as a 10.5 workbook since you didn't specify a version.
In your case it's more than just a blend as I initially suggested.
I made two separate data sources from the two sheets, and I made a 3rd data source that does UNION ALL of the two.
The UNION essentially adds one source to the other. Where there are common IDs between the two, you'll get two rows in the UNION data source.
Sheet 1 is simply a look at BASELINE. And I blended Update-14, and then gave a value where the ID from Baseline is also found in 14.
Sheet 2 is the opposite. Both sheets show that there are common IDs, and also that each data source has some unique ones.
That's why I made the Union. Sheet 3 displays all IDs, and the count of rows for each one. Where the value is 1, that's a unique ID in one or the other data source. Where it's 2, we have that ID in both.
Sheet 4 blends the two individual data sources to the UNION data source.
Notice the list of measures. (More than 4!) The first is the same value I displayed in sheet 3. The second (scroll over the second pill in the measures list) has a value if there is a row in Baseline. The third has a value if there is a row in 14. Notice the header on the sheet. The user can't tell which value is which. So Next I made a calc in the UNION data source. See [Records from BASELINE]. To create this, started it when I was in the UNION data source. Then once the calc edit window was open, I clicked on the BAASELINE data source and dragged [Number of records] from that data source into the calc editor. Tableau nicely created the whole syntax for me. Now I'm pulling the same value from the BASELINE data source that the second pill on the Measures shelf was getting. Only it's my own calc, and I can name it whatever I want. And you can see in Sheet 4 that I have that name in the header, and the value for my calc matches the value that the second Measures pill has.
Look at [Records from 14]. It does the same thing, only it pulls the value from the 14 data source.
At the bottom of Sheet 4 I added totals.
Look at Sheet 4(2). Here I took [Activity ID] off the sheet. Now I just get rolled up sums. There are 2311 records in Baseline. There are 2221 that have values in both data sources.
Go back to Sheet 4. Look at the calc for [Records in both]. This works on Sheet 4 because all the IDs are listed on the sheet. But if you were to add that to Sheet 4(2), you would get a null there. That's because [Records in both] has to evaluate at the ID level. When ID is off the sheet as it is in Sheet 4(2), tableau only sees the rolled up values. 2311 is not equal to 2270, so we get null.
So I created [Records in both V2]. Take a look at that. It is looking at a FIXED LOD value. These get evaluated at the specified level. Here I specified [Activity ID]. On every Activity ID this calc pulls the number of records for that activity ID behind the scenes. My calc says if the value is greater than 1 then set the value 1 (else leave it null.) Now putting SUM([Records from both V2]) can get rolled up on Sheet 4(2), and you'll have your number of common IDs in both data sources.
Using these, you can do the kinds of math you need. I'm not clear on what a successor is. If it's an ID that is in both, then your value is 2221.
But now let me show you something different.
I added another column to both your excel sheets. I called it "Source". For the Baseline sheet I set it to "B", and for the 14 sheet I set it to "14". You will see Source in the UNION data source. I made a new set of calcs in the union data source. They are all in Dimensions, and they all start with [Records ...]. I don't know if you have the liberty to add that column in your own data, but with that column I can eliminate all the blending, and just work with one data source. On Sheet 6 I displayed a bunch of values. My calcs can identify which Activity IDs are in which original excel sheets. I can tell you which are unique to Baseline, which are unique to 14, which are in both. This makes your analysis far easier, especially if your goal is to count up IDs that are in both, or are in just one sheet, etc.
Roberto Example.twbx 1.9 MB
Wow! This is quite a detailed response. Thanks a lot Joe. I'm going to look at it and try to understand how you did these. Will probably get back with some more questions. Thanks again!