7 Replies Latest reply on Jun 21, 2013 8:10 AM by laurel.shipley

    Using Lookup values in a Calculation

    laurel.shipley

      I have a list of customers and their latitudes/longitudes. I want to be able to pick a specific customer and given a chosen distance see the other customers that are within that distance. In order to do so, I have to calculate the distance for every record from the chosen customer's location.

       

      This problem is also described in this post:http://community.tableau.com/thread/109297

       

      Richard wrote "I do the lookup with data blending (by defining a calculated field in the primary data source which is just the value of the PostCode parameter) and then looking up the matching row by joining on that to the secondary data source with the post code locations.  A calculated field then gives the distance."

       

      I set up a my "Reference Customer" calc based on my Customer ID paramenter and I believe was able to successfully blend that to my secondary data source to get the Reference Latitude and Longitude. However, in my calculation, I keep getting an error because I am mixing aggregrate and non-aggregate arguments.

       

      What am I doing wrong? And how can I make this work?

       

      I'm new to Tableau, so any help would be greatly appreciated.


      Thanks.

        • 2. Re: Using Lookup values in a Calculation
          Richard Leeke

          Hi Laurel

           

          I'll take a look at your example and see if I can spot what's happening, but I'll also point you at a few other resources that might be helpful, in case you haven't seen these.

           

          Robert Mundigl has written several blog posts discussing how to do this sort of analysis on his Clearly and Simply site. I wrote some follow-up articles (here's the first of the series - there are links to Robert's original and the rest of mine) discussing ways to get around some issues Robert was having back when Tableau 6 came out. Those articles discuss various approaches to doing the lookup of lat/lon and calculating the distance.

           

          I did a viz of New Zealand earthquakes which includes another slightly different variant of the calculation (in this case the reference points were from another table, rather than being references back to other rows of the original table as in Robert's example - your example sounds more like Robert's).

           

          You can download sample workbooks from Tableau Public from those links.

           

          This thread, which started out discussing something quite unrelated evolved into a long discussion of more efficient ways to do this type of distance calculation if you have access to a database with spatial functionality (SQL Server, Postgres, Oracle, etc). I only mention this because depending on your data volumes, the lookup approach discussed in the first few threads just may not be viable. The lookup approach is fine with thousands or probably even tens of thousands of rows whereas the spatial database approach scales to millions.

           

          I've also just noticed that there are now a couple of Tableau Knowledgebase articles on Calculating Distance and Radius Filters. From a quick skim it sounds as if the second of those might be quite close to what you want.

           

          I'll take a look at the example you posted too.

          1 of 1 people found this helpful
          • 3. Re: Using Lookup values in a Calculation
            Richard Leeke

            You were almost there.

             

            The error about mixing aggregate and non-aggregate is a very common one when you are using data blending and it can be a bit confusing until you get your head around what is happening.

             

            Any references to fields from the secondary datasource have to be at the aggregate level. So even though in this case you have used blending to lookup the value of a specific reference customer, you still had to reference the average latitude and longitude. You could just as well reference the MAX() or MIN() or even better the ATTR() - they will all give the same value.

             

            The reason I said ATTR() is even better is that this is a special aggregate that will return the value if all rows have the same value, otherwise it will highlight to you that there are multiple values by displaying a '*' - which can be a useful way of spotting that you have something wrong (if your lookup had returned more than one value in this case).

             

            So as your reference lat and lon are aggregates, you need to make the lat and lon references to your primary data source aggregates, too. Just wrap them in the ATTR() function as I have in the attached.

             

            I also had to put your reference customer ID on level of detail to allow the blend to work.

            1 of 1 people found this helpful
            • 4. Re: Using Lookup values in a Calculation
              Richard Leeke

              Thinking about it, the approach discussed in that knowledgebase article is probably the best way of doing this.

               

              I've added another sheet which shows how to do that, using a new datasource which just joins your original table to itself and has a single-select filter to choose the reference customer. Notice that as this no longer uses data blending you don't have to worry about the aggregate functions in the calculation.

              • 6. Re: Using Lookup values in a Calculation
                Priya S

                Hi Laurel,

                    I am trying to do exactly the same thing, but the problem is I am trying to connect to Salesforce instead of Excel.

                How did you write the distance formula? I am new to Tableau as well. Can you pls help?

                • 7. Re: Using Lookup values in a Calculation
                  laurel.shipley

                  If you open the workbook on my original post, the "Distance from Customer" measure contains the distance formula.