How about the attached?
I changed the relationship for the join to only "Subscriber ID" (breaking the other sheets in the attached), and made use of table calcs to generate the results I think you are looking for. Both table calcs have their compute using set to "Subscriber ID", so that dimension will be used for addressing, and all others on the worksheet will be used for partitioning.
I also included another worksheet, so you can see that this data blend can be performed in either direction.
Great stuff, thanks!
Couple of questions:
- Do you see any reason why my first stab at this *should* have caused the strange behavior? It seems including the "IsActive" caused some sort of odd filtering behavior above and beyond the JOIN
- On the table calc:
IF FIRST()==0 THEN
Are the start/end parameters on WINDW_SUM strictly necessary, or would this have worked just as well:
IF FIRST()==0 THEN
Also, what does the top-level IF FIRST()==0 statement buy us? Is this IF and the Start/End parameters on WINDOW_SUM something you put in, or just an artifact of the way Tableau builds an expression for you when you right-click on a measure and create a quick table calc? What sort of a Table Calc was this if you started from one of the selections on the menu?
Lots of great questions! Thanks! :)
Here are some of the table calc concepts at play.
> just an artifact of the way Tableau builds an expression for you when you right-click on a measure and create a quick table calc? What sort of a Table Calc was this if you started from one of the selections on the menu?
This did not come a Quick table calc, the lineage of this calculation style is from Richard Leeke, see http://www.clearlyandsimply.com/clearly_and_simply/2011/01/another-look-at-site-catchment-analysis-with-tableau-6-part-3.html for additional details. I wrote these to fit your situation.
> what does the top-level IF FIRST()==0 statement buy us?
When you add "Subscriber ID" to the level of detail, you are now dealing with more than one mark in the "cell" (the distinct combination of values from the dimensions on the Columns and Rows shelf), Since you only want one mark (one bar) per "cell", the
IF FIRST()==0 THEN [stuff] ENDstatement is effectively saying: If this is the first mark, return [stuff], otherwise return NULL. We can then use the formatting of the measure pill, and Pane tab to Hide the Special Null marks, and turn off stacked marks to get the mark labels at the tops of the bar marks instead of in their interior.
> Start/End parameters on WINDOW_SUM
You could use no start and end arguments like:
IF FIRST()==0 THEN WINDOW_SUM(SUM([Profit])) / WINDOW_SUM(SUM([Revenue])) END
instead and get the same results. See the article by Richard that I linked to above for details. It is something I normally use in my table calcs because it ensures they will be evaluated efficiently.
> Do you see any reason why my first stab at this *should* have caused the strange behavior?
I do not understand your question, can you please rephrase it with additional details?
Joe, sorry. I was moving too quickly when I responded.
The "alternate" expression that I meant to paste in was one w/o parameters oin WINDOW_SUM:
IF FIRST()==0 THEN
I accounted for that in my reply above, and in this latest reply, you currently have too many parentheses, this would cause it to error.
Sigh! Moving too quickly once more! :)
RC: Do you see any reason why my first stab at this *should* have caused the strange behavior?
JM: I do not understand your question, can you please rephrase it with additional details?
It probably is wise to take a step back so you see where I'm coming from: In a perfect world, I would have leaned only on SubscriberID as the unique key JOINing my two tables. I didn't because I couldn't figure out how to join at one granularity (SubscriberID) yet aggregate at another (IsActive, LineofBusiness, PhoneName, Region, and the 2-3 other fields which are common across both dimensions). You showed me and are my hero :)
Since I was "forced" to JOIN by leaning on a compound key/relationship of (LineOfBusiness, PhoneName, PhoneType, SubscriberID, IsActive, Region, State), I ran into the behavior I originally posted about. Somehow, including IsActive as one of the components of the relationship "broke" Tableau's ability to completely blend the metric (Profit Margin %) which came from the secondary data source...note the Grey bars with no labels for the "Churned - Not Active" column group.
When I removed IsActive from the "Compound Key" which joined my dimensions, the "blended metric" worked everywhere (even though the values were wrong since I pretty much changed my join and therefore what rows were included in the calculation).
What doesn't make sense to me is why including or not including IsActive in the set of relationships changed Tableau's abiity to blend "correctly". It seems as a slice of the rows with a particular IsActive value were filtered out...which shouldn't have happened since the values for each SubscriberID are the same across both tables....
Of course, this is completely a moot point now that you've shown me the way to build a better mousetrap, but the behavior still doesn't seem right to me.
Clear as mud?
And again, many thanks for the links. I'm going to jump out there right now and download / read / consume everything I can find.
> Clear as mud?
Yes, but I will try to address your concern.
When you have laid out your worksheet with a data blend, and you select a data source with an orange check mark, you will see dimensions listed with a little red chain-link icon. These are the fields the data blend join is being performed on. This join is specifically a Left Join, meaning all records in your primary data source will be available, and only records where there is a match will records from the the secondary data source be available.
Perhaps what happed was with the additional dimension, Tableau created a join on that field in addition to the other, but caused an unexpected result for you, because that join did not make sense for your data structure.
You can specify the fields to join on by editing the relationships between the data source in a few places, one option is to right click either of the data sources and select Relationships.
There a few great resources on data blending in Tableau in the knowledge base, some can be found at: http://www.tableausoftware.com/search/kb_article/data%20blend