6 Replies Latest reply on Dec 29, 2016 1:03 PM by marcin.m

    Lookup previous Latitude and Longitude from Data Source

    marcin.m

      Hello,

       

      I am trying calculate the distance in miles between 2 locations for 2 consecutive fuel purchases – each transaction is stored in one row in Source Data. I am using a geometric formula for distance between 2 points on a sphere (Earth). Creating that formula is easy (I have Latitude and Longitude values) in Source Data below as Lat and Lon (far right of the image) and I am using this formula:

       

      3959 * ACOS

      (

      SIN(RADIANS(AVG([Lat]))) * SIN(RADIANS([Tab-PrevLat])) +

      COS(RADIANS(AVG([Lat]))) * COS(RADIANS([Tab-PrevLat])) * COS(RADIANS([Tab-PrevLon]) - RADIANS(AVG([Lon])))

      )

       

      To get latitude and longitude of previous transaction (Tab-PrevLat and Tab-PrevLon), I am using a Lookup function – and this is where I think the problem is. I am not getting expected results.

       

      1.png2.png

       

      I looked for the answer in this forum and someone (in response to a different question) mentioned that lookup formula should be "computed along Table (down)" - not sure how to do that.

       

      Also, I need to make sure that I am only calculating distances between transactions for the same vehicle (in this example distance between first and second transaction (row) below for vehicle 14066, I don’t want to calculate the distance between row 3 and 2 since that would be calculating it for different vehicles – 14066 vs. 14077). 

       

      3.png

       

      I also don’t want these distances to be dependent on filters (for example: if I am filtering some transactions out – like not showing row 4, I still want the distance of transaction 5 to be calculated based on coordinates for transactions 5 and 4, even if transaction 4 is not shown in my dashboard)

       

      I hope this is not too confusing. I looked online for answers but I am not seeing anything that specifically answers all of these concerns.

       

      I am attaching the Tableau file - I have previous Lat and Lon in there but it was done in excel to check my Tableau code - which bombed.

       

      Any help would be appreciated.

        • 1. Re: Lookup previous Latitude and Longitude from Data Source
          Joe Oppelt

          (Note to self:  10.1 workbook here.)

           

           

          You get previous values by using the LOOKUP function.  It's a table calc.  But a table calc isn't going to go back to the data source itself.  it will go to the underlying table for the sheet.  And if you have filtered out actual rows, they will not be in the underlying table for LOOKUP to go back to.

           

          In Sheet 2 of the attached, I have created a sheet similar to your screen shot.  And I used LOOKUP to get the previous value.  By default tableau sets up the table calc to run DOWN the rows.  You will notice, however, that the calc isn't caring about the vehicle number.

           

          In sheet 3 I modified the table calc to restart with every vehicle number.  You will see, therefore, that the first value for each vehicle is NULL since there is no prior row to draw from.

           

          And finally, in Sheet 4, I created a filter that does NOT eliminate actual rows.  If you use a table calc as a filter, it leaves the underlying table for the sheet intact, but just displays what fits the filter criteria.  In this example I am filtering on BRAND.  You will notice that if you unselect 7-11, the sheet still shows the previous latitude for Vehicle 14066 (which was a 7-11 store.)  If I had simply done a quick filter on the [Brand] dimension, the 7-11 rows would have been excluded from the sheet AND from the underlying table, and the value of [Previous Latitude] for the Conoco would have ended up being null.

          1 of 1 people found this helpful
          • 2. Re: Lookup previous Latitude and Longitude from Data Source
            marcin.m

            This is great! I think Sheet 4 is the closest to what I need. I will have to modify it slightly to not group it by brand by arrange transactions by date (in ascending order). I only care about distance between consecutive transactions. I think I can figure it out.

             

            I also need to find out how to create a filter that is a table calculation but I think I can find that somewhere.

             

            I think I can use this newly created table to derive a distance between coordinates but would you know if I would be able to use this table as a basis for the map I've created and store these distances in a measure to use these in tooltip?

             

            I will mark your answer as correct since it did answer my questions.

             

            Thank you.

             

            EDIT. Here is how I modified Sheet 4 to meet my needs (Distance 3.twbx) and included a "check" from Excel - it works. Would you know how to now treat this new table as a basis for the map or including this "DistanceTableauCALC" in another table?

            • 3. Re: Lookup previous Latitude and Longitude from Data Source
              Joe Oppelt

              Any table calc used as a filter will work the way mine did.

               

              The one I made uses LOOKUP() to filter.  I just threw ATTR([some dimension]) in the first argument for LOOKUP, and used offset 0 as the second.  Here's how this works:  It says, "If the [dimension] of the current row is one of the ones the user selected, the grab it."  And it does that for all rows.  Display that filter to the user, and he will get a list of all valid dimensions.  Whatever he selects from the list will be displayed on the sheet.

               

              And yes, you can certainly use this on a map.  And the table calc values will display in the tooltips.

              1 of 1 people found this helpful
              • 4. Re: Lookup previous Latitude and Longitude from Data Source
                marcin.m

                Thank you. I've edited my previous response and added the Distance 3.twbx file with modified Sheet 4. If I go back to my "6 months" sheet and try to add "DistanceTableauCALC" to tooltip, it does not work. I may be making some small mistake somewhere.

                • 5. Re: Lookup previous Latitude and Longitude from Data Source
                  Joe Oppelt

                  See Sheet 6

                   

                  At first I tried shoving it in your original map, but I couldn't make it work.  So I rebuilt the map, and in doing so, I realized that the [Tableau distance] calc is built on two other table calcs.  (Previous LAT and Previous LON.)  When you have that, you have to set the table calc settings on both the underlying calcs.  I didn't look carefully enough at that.

                   

                  You set each by selecting each one individually in the "edit table calc" screen.  (See image to find how you do that.)

                   

                  1.png

                  So I could have probably done the same in the original sheet too, but I have it in Sheet 6, and I left it at that.  Those table calcs don't have to be on the sheet anywhere, by the way.  Just the one you want to display.  It probably worked for you on Sheet 4 because the two calcs were already on the sheet and set with the proper settings when you added the distance calc, so Tableau picked up the settings from them.  But when I added the distance calc to a sheet that didn't already have the underlying calcs, Tableau just picked its own default and ran with it.

                  1 of 1 people found this helpful
                  • 6. Re: Lookup previous Latitude and Longitude from Data Source
                    marcin.m

                    It worked perfectly when I tried to replicate your steps without in my "Fuel purchases in past 6 months" sheet. Thank you.