Ben, try using this structure:
IF x=y AND NOT y=z THEN 'A'
Thank you for getting back to me,
i have been trying to workout what you mean with the structure above, am i too add this to my existing CALC, or re-build the whole field from scratch. I have kinda tried both, but one does nothing and the other just gives the end status for all. :-s
Post a sample packaged workbook and I'll see if I can work it out for you.
I think you probably mean the code below. The symbol ! means NOT in comparison below. You can use <> but != is slightly more meaningful.
ATTR([Series Title]) != ATTR([Sheet1 (Operator_A.xlsx)].[Series Title]) THEN 'OPA Remaining'
ATTR([Series Title]) != ATTR([Sheet1 (Operator_B.xlsx)].[Series Title]) THEN 'OPB Remaining'
ELSE 'Series Complete'
I tried that, but i think because of the ATTR, any NULL fields are ignored by the calculation. so all i end up with is the final ELSE, in this case Series Complete.
i think i need the ATTR though as i am cross referencing 2+ Excel Docs..
Please see demo work book attached,
As you can see it shows Series that are "DONE", series that are half done (IE on 2 of the Excel Docs), but it does not show ones that have not been started (Pending)
(in this case the imaginative series of "Who Loves Coffee" and "Generic Office Stuff") I would like these to show on the same graph but under Pending or OP A pending and OP B Pending etc..
I hope that makes sense?
Sample Tableau Work.zip 26.6 KB
I'm guessing i am asking the impossible then? no worries, thanks for looking.
Ill see if there is another way around the issue.
This works at comparing 3 Excel docs, by seeing if there are any matching fields in Series Title column and then applying on of the 3 status. However this only works if there IS the series title in the main doc and one of the OP docs. if the series title is not in the main doc and in on of the OP docs then it just ignores it
This is exactly how data blending works in Tableau. The dimensions from the primary data source set the granularity (level of detail) of the view (by view I mean Rows, Columns, Pages, and Marks Card). Dimension values from the secondary data source cannot increase the level of detail of the view. If a linking dimension value is in a secondary source but not in the primary, Tableau will ignore that value from the secondary.
To explain in more detail, here's what Tableau does as far as I've been able to determine:
-Generates a query on the primary data source, using the dimensions in the view in the GROUP by clause to aggregate the measures.
-Generates a query on the secondary data source, using the linking dimensions to generate a GROUP by clause to aggregate the measures from the secondary.
- Blends the data together along the linking dimensions, any tuples (results from the query) from the secondary that don't exist in the primary are ignored. Any tuples in the primary that don't exist in the secondary will end up with measure values of Null.
- If the set of linking dimensions is different than set of dimensions in the view, then Tableau performs its own re-aggregation of the measures from secondary sources to the level of detail of the dimensions in the view.
In order to get the results you want, you'll need to do some work in the data connection or outside of Tableau. For example, you could left join the A and B sheets to the primary so you'd have a single set of values. If those Excel workbooks have to be kept separate, you can create linked worksheets to have a federated data source.
I think i got you, so because their is a link to all 3 sheets via client then it will only show the values that match via client and my calculation, thus ignoring the ones on the sub sheets as there is not match on the main sheet.
I like you idea about a blended data source, that could work i guess. ill see what i can come up with.
I think you've got the essentials. Good luck, and post again if you have any questions!