5 Replies Latest reply on Oct 29, 2018 1:42 PM by swaroop.gantela

    Calculated field if/when

    Heidi Kiser

      I'm using 18.2 and have been creating calculated fields for about 6-12 months, but still have problems in some cases.

       

      In this case have fields for Flights (Airline, Departure Airport, Arrival Airport, Airplane Type), Airports (Identifier, City, Country), Regions (Country, Specific Regions (17 in all)).

       

      As you can see in FlightsFromTo workbook, I have the [Airline], [Specific Region], [Departure Airport], [Specific Region], and [Arrival Airport]. I don't want to stack the fields in Rows like this though.

       

      I need to two calculated fields, [Departure Region] with the [Specific Region] for the  [Departure Airport] and the [Arrival Region] with the [Specific Region] of the [Arrival Airport]. Then I need to be able to select in two filters for [Departure Region] and [Arrival Region], so I can identify all flight combinations of airports that fly between those two regions. How do I make the [Departure Region] and the [Arrival Region] calculated fields?

       

      When I try IF [Departure Airport], THEN [Specific Region], it expects a boolean value to compare to [Departure Airport]. CASE doesn't work either.

       

      My output would ideally say something like:

       

       

      Airline                      Departure Region     Arrival Region       Dep Airport      Arrival Airport   Aircraft Type

      American Airlines:

                                      North America          Europe

                                                                                                     CHICAGO  to  LONDON    Boeing 787

                                                                                                     BOSTON to AMSTERDAM   Airbus A380

                                     North America          South America

                                                                                                    HOUSTON to SAU PAULO  Airbus A330

                                                                                                    PHILADELPHIA to LIMA   Airbus A340

      etc.

       

      Thank you in advance for your help!!!  The packaged workbook is attached.

       

      Heidi

        • 1. Re: Calculated field if/when
          Santiago Sanchez

          Hi Heidi,

           

          For what it's worth, I've been working with Tableau for a few years now and I still run into calculations that take me a while to figure out. In a way, that keeps it fun!

           

          That said, I wonder if you don't need a calculation for this filter. Can you please take a look at the workbook attached? It's using both regions just as filters and gets us to the result below:

           

          Would this work?

           

          Thanks!

          Santiago

          • 2. Re: Calculated field if/when
            Heidi Kiser

            Santiago, Thank you for your reply!

             

            About Using Filters

            Hmmm, I didn't know Tableau would allow me to apply the same filter twice, and then apply them to the correct airports in the [From-To] field. In fact I don't think it does actually.

             

            Unfortunately, if you look at the relationships, the "Regions" source are regions related to the Airline's domicile (home base country), and the "Regions1" source are the regions related to the Airports that the fights go to or from. So using the fields [Specific Regions] as the "From" filter give me only those flights from the airline's domicile, and [Specific Regions (Regions1)] gives me the flight region for one of the two airports. Notice in your snapshot above, Advance Air is based in the U.S. but is flying between Athens (ATH) and Kyiv (IEV) in the Ukraine, both in Eastern Europe.

             

            Tableau will not allow me to apply the same field [Specific Regions (Regions1)] twice in the Filters. If I duplicate the [Specific Regions (Regions1)] field and name it the "B Region", Tableau still only looks at the regions that are selected in the [Specific Regions (Regions1)] filter and does not consider the contents of the "B Region". So I don't think this will actually work, unless I'm doing something wrong with the filters.

             

            About Parameters

            I've never actually successfully applied parameters before. It seems this should work, but then some of the results are not accurate. The first Air France flight is ATL Atlanta, GA to PTP Pointe-à-Pitre, Guadaloupe. Obviously, ATL is not in Western Europe. The strange thing is a lot of the FROM-TO combinations are correct, but there are enough that are inaccurate (knowing all the airport codes as I do), that I cannot accept this solution as is. Do you know how to correct it? Or are we back to needing a way to create a calculated field that somehow combines the correct region with the departure and arrival airports?

             

             

            Again, I really appreciate your efforts!!! This is going to be used for planning a business software to the correct airline fleets, and the results must be 100% accurate.

             

            Thank you,

            Heidi

            • 3. Re: Calculated field if/when
              Santiago Sanchez

              Hi Heidi,

               

              I was assuming [Specific Regions] referred to the departure airport while [Specific Regions (Regions1)]  referred to the arrival airport, but sounds like that was a bad assumption

               

              In your data set, how would you recognize an arrival region vs a departure region? Tableau won't let you apply a filter twice because a filter essentially removes rows, so if you filter out a row based on the departure region, that same row can no longer be filtered by the arrival region. I believe we either need 2 columns or 2 rows to figure this out, but I don't know your data set well enough to identify if these exist.

               

              Best,

              Santiago

              • 4. Re: Calculated field if/when
                Heidi Kiser

                Santiago,

                This is a diagram of the relationships in the data. Notice I have both [Departure Airport] and [Arrival Airport] linked to the WorldAirports table, which supplies the airport [Country], which is then related to the Regions1 table [RegCountry] in order to organize by regions around the world.

                 

                I have Regions1 [Specific Regions] and created a calculated field for [Specific RegionsB] to get two separate airports. Will this work? How else can I get two separate filterable fields? 

                I still believe this would be more easily solved if I could create a calculated field that combines values of [Departure Airport] and [Specific Regions] to equal [Departure Region], and the same for [Arrival Region]. Is this not possible?

                 

                Thank you again!

                Heidi

                • 5. Re: Calculated field if/when
                  swaroop.gantela

                  Heidi,

                   

                  Apologies, I'm late to the conversation and have not caught all the intricacies.

                  I was wondering if it may be helpful to map it out in Tableau Prep.

                  That way you could twice connect to the world airport table,

                  once on depart airport, then separately with arrive airport,

                  and then merge those two together.

                   

                  I think something similar to this helped in this post:

                  Data Join Issue

                   

                  If that doesn't work, will try again if you could post some

                  small mock datasets of the allflights and world airports tables.