I suspect this is very simple, but is beyond my skills.
I am trying to build an If formula but can't get it to do what I want. I am using Tableau for an ancient history PhD which has not yet been published, so I can't publish my live data. Attached is a very simplified version of the data, using titles which would probably be easier for you to understand than my data anyway.
The data shows 4 people and the various states in which they held political positions such as Governor, Mayor of the capital of the state, Lieutenant governor or some other title. Slightly unrealistically for those positions, but makes sense for my data, many of them have held positions in multiple states. For each person, the data has one row for every title they held, in every state (for example, Mr C has 3 rows of data all in one state, so he holds 3 titles, Governor of Texas, Mayor of the Capital of Texas and one other title which is neither Governor nor Mayor but is in Texas).
I want to create two new calculated fields:
- Field 1 will analyse whether they held the position of Governor or Mayor of the capital of the state, showing one of 4 possible values:
- Both (ie Governor of the state and Mayor of the Capital of the state - only if the same state - eg if some one is Governor of Utah and Mayor of New York they should not show as both, they should show as Governor of Utah and as Mayor for New York, but not both for either state)
- Field 2 is just a slightly more complicated version of Field 1, and just deals with 3 possible positions, so would return one of the possible combinations:
- Lieutenant Governor
- Governor and Mayor
- Governor and Lieutenant Governor
- Lieutenant Governor and Mayor
- All 3 titles
You will see I have created a calculated field which attempts to do Field 1, but it puts the same value into all states, not just the specific state. For example Mr B is the Mayor of the capital of Florida and the Governor of Utah, so should only have single values against those provinces, not all provinces, and in those provinces it should not be Both, but rather the respective titles held.
The final two fields in the worksheet are my manual calculations of what the results of Field 1 and Field 2 should be if I could get the formula to work
Can some one suggest how I can create Fields 1 and Field 2? I would be very grateful.