4 Replies Latest reply on Aug 10, 2018 6:02 AM by Zhouyi Zhang

    Help with an If formula

    Victor Clarke

      Hi guys

       

      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:
        • Governor
        • Mayor
        • 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)
        • Neither
      • 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:
        • Governor
        • Mayor
        • 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.

       

      Thanks

       

       

       

      Victor

        • 1. Re: Help with an If formula
          Zhouyi Zhang

          Hi, Victor

           

          Please find my solution attached. below is the result for your reference.

           

           

          Hope this helps

           

          ZZ

          1 of 1 people found this helpful
          • 2. Re: Help with an If formula
            Victor Clarke

            ZZ

             

            Thanks

             

            Looks great - will try to apply it to my data over the weekend, and then post on the Forum

             

            Regards

             

             

             

             

            Victor

             

             

             

             

             

            Victor Clarke

            Ph: 041 821 7896    |    Email: clarke.victor@gmail.com    |    Photos Website: www.victor-clarke.squarespace.com

            • 3. Re: Help with an If formula
              Victor Clarke

              ZZ

               

              Firstly thank you - I have applied this to my data and it worked perfectly.

               

              I had some knowledge of LOD calcs using the {Fixed etc formula, but was not aware you could put multiple fields into it like this!

               

              I had one minor challenge when I ran it on my live data - because some of my data has a large number of lines for each person, the formula { FIXED [Catalogue number],[Name],[State]:SUM(IF [State] = [Governor of State] THEN 1 ELSE 0 END)} returned values > 1 for some of the individuals (ie there was more than one line with a value of 1, and as a result the formula got a number greater than 0.  You were expecting 0 or 1 which were the only possible values for the test data I gave you. I solved that by changing the formula to { FIXED [Catalogue number],[Name],[State]:Max (IF [State] = [Governor of State] THEN 1 ELSE 0 END)}. In that case if there are more than one line returning a 1 into the formula, it takes the highest value, which in all cases is either 0 or 1.

               

              One minor question - you added a field calculation1 which has the following formula IFNULL([Governor of State],[Mayor of state capital]) As far as I can see, you didn't actually use that field at all in the final result - am I right or am I missing something?

               

              Thanks, I really appreciate your help

               

               

               

              Victor

              • 4. Re: Help with an If formula
                Zhouyi Zhang

                Hi, Victor

                 

                you are welcome, and I am glad to help out.

                 

                to your question, if you have multiple rows returned in case, you probably can try use countd() and count the unique name as shown below  instead the original one to all the 3 calculation fields.

                 

                 

                and the calcualtion 1 is regardless, please ignore it, that's my first try and didn't clean it up.

                 

                ZZ