Also to note that I need to maintain it as a Dimension rather than a measure.
1 of 1 people found this helpful
it's pretty hard to answer without seeing the data and datasource but have you tried
if min([Business Unit])="AC" or min([Business Unit])="AH" or min([Business Unit])="PH" or min([Business Unit])="GH" Then "Corporate"
Thanks for the suggestion, but unfortunately that brings the Line of Business into the Measures area.
I think I'll revert to bringing the Excel file into a Union with the R file, which will work but makes updating more of an issue.
Could you post a twbx file ?
Sure. Thanks Maciek.
I attach a sample here.
In this example I'm trying to chart it so I can see total revenue from all 3 sources over as long as data is relevant for all 3 sources.
I have written "Sum of Revenue" in an effort to do this:
IFNULL(SUM([Sheet1 (Sample 2)].[Revenue]),0)+
IFNULL(SUM([Sheet1 (Sample 3)].[Revenue]),0)
However, the charts I make for this do not combine the revenues from the different sources. I have linked the files in an effort to do this.
A simplified view of Tableau data blends is that they are form of left-join *after* aggregation, so there's now way to work with fields across joins at a record level, we must use aggregates.
However, in this case we don't need to do that aggregation in a calculation because the Line of Business can be used as a linking dimension while in the primary source a record-level calculation can be used. Here's an example where I used a record-level IF statement to create a Corporate/Retail dimension:
There is a problem with this approach around sparseness of data. If the primary source doesn't have a Line of Business/date combination that exists in one of the secondary sources then that data won't be included in the view. For example the Sample1 data is all for 2015 and we don't see that at all in the above worksheet that uses Sample3 as the primary source.
So if you're going to stick with a data blend solution I suggest you build a "scaffold" source that has all the LoB/date combinations and use that as the primary source. The even better approach, though, would be to do a full union of all three sources. Unfortunately we can't do that (yet) in Tableau's interface, as of 10.1 we can union inside Excel, text, and Google Sheets sources and in 10.2 we'll be able to union inside databases, I don't know when we'll get cross-database unions.
Tester.twbx 206.2 KB
Thanks so much for taking the time to give such a helpful and detailed reply.
It has given me clarity of approach in terms of what I need to do.
I'll keep an eye on updates with this in mind. The initial build stage now will be focused on incorporating the Excel files into the RData file.
Thanks again Maciek. Interesting read, the angle of conditional formatting is a useful one, I'll give those methods a blast.