The short answer is yes. If you post a bit data, sample spreadsheet or the start of a workbook, you'll make it easier for us to answer your question.
Hi John. Please, post a packaged workbook (.twbx) instead of a .twb file. The packaged workbooks include all the data. If you're working in Ver. 6.1 you'll find this in File/Export/Packaged Workbook. If you're working in Beta 7 it'll just be File/Export Package Workbook. Thanks --Shawn
John, I took a Joe Mako solution and applied it to your data. As Joe points out Sets aren't available in calculated fields so you have to do the filter logic using a lot of CASE and IF statements. I did the first four of your sets, you'll need to do the rest. It's a lot of cut & paste, but it does what I think you want. I'll add a 'Wouldn't it be nice' post about using Sets in calculated fields. Here's the original Joe Mako solution and discussion:
Hope this is what you were looking for. --Shawn
john-schools.twbx 8.9 KB
Thank you. I appreciate your help. It is very close to what I'm looking for.
Is there a more "scalable" solution to the problem? For example, if I have 1000 schools and 30 peer schools for each school. In such case, hard coding all possibilities isn't really optimal.
Is it possible to lookup the selected school against the data, match that record, and then show only the peer schools associated with that record? I have looked through the documentation and just can't figure out how. I thought the LOOKUP function might work but that seems to work different in Tableau than in Excel.
Again, many thanks. Your help is appreciated.
You can do it much more easily with a parameter to choose the school and then a few calculated fields to pick the chosen school and its peers, as attached.
Even this will get a bit unwieldy with 30 peers with the data structured as you have it. It would be much better to restructure your data in this case to be "deep" rather than wide. So instead of your original example:
School Peer1 Peer2 Peer4 Peer5
A1 B1 B2 C3 C4 D3
B1 B2 A1 C4 D3
D3 A1 B1 B2 D4 F5
Structure it like this:
School PeerNumber PeerSchool
A1 1 B1
A 2 B2
A 3 C3
A 4 C4
A 5 D3
B1 1 B2
B1 2 A1
B1 3 C4
B1 4 D3
D3 1 A1
D3 2 B1
D3 3 B2
D3 4 D4
D3 5 F5
You may not need the peer number at all.
sampledata_rl.twbx 21.3 KB
That's perfect! I can easily restructure my data as you suggested.
Many many thanks,
The other thing I should have mentioned with restructuring it like that is to split it into two tables (which may be 2 sheets in a spreadsheet). One has one row for each school and holds that school's type and scores, the other just has the peer relationships, as per my example above. Your Tableau data source would then be defined as a join between your two tables. That way avoids you having to maintain duplicate school data.
Hey Richard / John,
Small question, in line with the above solution. If my peer set was to be variable, i.e. For A1, I should be able to choose B1 B2 C3 C4 D3 or B2 A2 C4 D3, essentially be able to select any peers for a given school. Can we do this / have you guys tried it?