Richard the easiest way to do this would be to reshape your data. If your data is in Excel install this Reshaper If not, you might be able to use combine fields and then a CONTAINS() test in a calculated field. Post a bit of sample data or better a sample packaged workbook and I'll take a look.
I have attached a small set from the actual data I am working with. I have highlighted the field by groups. The Fields in green have a 1 to 1 relationship with the application ID in Column B in the spreadsheet. The Fields highlighted in Blue and Orange have the same fields but they are double and triple the fields.
For the ones in Blue and Orange I will like to group and get the sum of Yes and No and Nulls.
For Example, I would like the Blue Group to group together Columns P and Q, Columns R and S, Columns T and U and so forth.
In Columns P and Q for one row you could get 2 nos, or 2 yes, or 1 no and 1 yes but I would like the field to count the total for the field.
Currently tableau is counting them separately or trying to count the combinations.
Eventually I would like to be able to report a 1 to 1 like I am able to do for the Green Group which is currently at a 1 to 1 relationship for the data and not a 1 to 2 or 1 to 3 for the Blue and Orange Groups respectively.
Still a newbie to this and any insights is helpful.
Client Data Tableau.xls 502.0 KB
I am still using Tableau 7 and I think you created this solution in Tableau 8.
Would you mind saving it in version 7.
Also can you walk me through how you used the reshaper in excel. i tried using it myself and was unsuccessful.
Here you go Richard. In the reshaper select cell J2 then click the Reshape Data button on the Tableau tab:
If you don't see the Tableau tab, then go back and follow the install directions a bit more carefully. After T creates the new Client Data-Tableau sheet, you'll want to change Col1 and Col2 to Questions and answers respectively.
Let me know if you have more questions.
Would you also mind saving the Tableau workbook you created for me in Tableau 7?
I don't have access to Tableau 8 yet and getting an error when I try to open it.