10 Replies Latest reply on Sep 18, 2018 5:10 PM by Shinichiro Murakami

    Distance Calculation Using LOD Expressions

    David Weaver

      I am stumped and would appreciate some help. Please refer to the attached excel table. I have a large number of Locations (ID Column) with Values and Lat/Longs associated with each. For each Location I need to determine the average Value of all Locations that fall within a parameter established radius of the subject Location (yellow column). So for instance, in the case of Location A this would require that distances to the other 25 locations be determined. Let's assume that half of the 25 locations were less than a parameter established distance of 2 miles from Location A and the average Value associated with those locations would be the value I am trying to calculate (and would go in the yellow column). The process would then have to be repeated for each of the other 25 Locations.

       

      I believe this can be done with LOD expressions but i haven't gotten past that. I would have attached a packaged workbook but it seemed easier to explain with the excel file. Thanks in advance for help.

        • 1. Re: Distance Calculation Using LOD Expressions
          Shinichiro Murakami

          HI David,

           

          This is very rough calc based on a certain latitude point (not far from your location)

          So you need minor modification a little anyways,.

           

           

          distance - Calculating longitude length in miles? - Geographic Information Systems Stack Exchange

           

           

           

           

           

          minor (?) Delta

           

           

           

          If this helped, please marl my answer as correct / helped to close the thread, from original post, not inbox,

           

          Thanks,

          Shin

          • 2. Re: Distance Calculation Using LOD Expressions
            Hari Ankem

            Would showing the points on a map help too?

            1.png

            • 3. Re: Distance Calculation Using LOD Expressions
              Shinichiro Murakami

              HI David Weaver

               

              Just following up.

              If you have further questions, please reply to this.

              If you think problem is solved, please mark my answer as correct / helped to close the thread not from inbox but from originals post.

               

              Thanks,

              Shin

              • 4. Re: Distance Calculation Using LOD Expressions
                David Weaver

                Shin

                 

                First let me thank you for helping me.

                 

                I have incorporated your input but now have run into another roadblock which I hope to explain with the attached Tableau workbook and accompanying excel data file.

                 

                I should also point out that I am using a “join” to accomplish what you had done by, I believe, creating a duplicate of the file. I tried that but the actual data  files are so big I experienced significant computing delays while has not been the case with my creation of a separate file that only contains the Lat/Long data and join fields. I hope this data preparation approach is not causing the problem described below.

                 

                My objective is to create a “type curve” which represents the oil production that a new well  (the “Valuation Selection”) could be expected to produce based upon an average of what similar wells in the immediate area (“Analogs”) have produced. The “Distance” calculation you helped me with determines which of the offset wells shall be considered an “Analog”. The problem then becomes determining the average oil production for the “Analogs”, including the “Valuation Selection”, on a “Month of Production” time sequence.

                Please refer to attached Workbook, where worksheet “Distance” shows the Distance to offset wells that fall within 2.5 miles of each Valuation Selection and are therefore considered “Analogs”. You will notice that only two of the Valuation Selections have been chosen. On the “Map” worksheet, the locations of the two Valuation Selections are shown, along with the Analogs that fall within the Distance from each Valuation Selection. So for so good.

                 

                On the “Oil Prod” worksheet I want to show the average oil production for the Analogs on a “Month of Prod” basis. The top graph correctly reflects oil production for the two Valuation Selections without any regard to the Analogs. But the bottom chart does reflect the average oil production for all wells falling within X miles of the Valuation Selections, as I wanted it.

                The problem seems to be with the “Average Oil Prod Within X” calculation. I have tried, unsuccessfully, various LOD Expressions to solve this problem and would again appreciate your help. And, anybody else that wants to join in the conversation is welcome.

                 

                Thanks Again,

                David

                • 5. Re: Distance Calculation Using LOD Expressions
                  David Weaver

                  See attached excel file i neglected to attach to previous message

                  • 6. Re: Distance Calculation Using LOD Expressions
                    Shinichiro Murakami

                    Not exactly sure, but you mean this ?

                     

                    Both top/bottom gray is same line. Top chart is only for the purpose of easier visual validation.

                     

                     

                     

                     

                     

                     

                    Thanks,

                    Shin

                    • 7. Re: Distance Calculation Using LOD Expressions
                      David Weaver

                      No, i am sorry for not making it clearer. The bottom chart  (Oil Prod worksheet) should reflect the average of the Analogs for each of the Valuation Selections. So, if the Apollo Unit is one of the Valuation Selections ( the orange line in this worksheet) the bottom chart should be the window average of the Apollo Unit Analogs which can be found on the Distance worksheet and are shown in orange--these Anlaogs to the Apollo Unit all are within 2.5 miles of the Apollo Unit. Likewise, the green line would reflect the window average of the Atlas Unit Analogs which are in green on the Distance  worksheet. You can also look at the Map worksheet to see the two clusters of Analogs, one around the Apollo Unit (in orange) and the other around the Atlas Unit (in green).

                       

                      And of course if i were to pick another Valuation Selection, say the Bean Hedtke Unit, this would create another cluster of Analogs within 2.5 miles and there would then be a third line on the bottom of the Oil Prod worksheet representing the average oil prod/Month of Prod for that Unit.

                       

                      I hope this helps,

                      David

                      • 8. Re: Distance Calculation Using LOD Expressions
                        Shinichiro Murakami

                        Bit confused as you expect.

                         

                        From below table on period "1"

                        APOLLO UNIT's average is average of red square, or you add some calculation beforehand with using distance or something?.

                        .

                         

                        Thanks,

                        Shin

                        • 9. Re: Distance Calculation Using LOD Expressions
                          David Weaver

                          Shin-We're getting there. I am out of town and will review tommorow and

                          then circle back with any questions. Thanks so much for your help,

                          David

                           

                          On Fri, Sep 14, 2018 at 10:21 PM Shinichiro Murakami <

                          • 10. Re: Distance Calculation Using LOD Expressions
                            Shinichiro Murakami

                            HiDavid Weaver

                             

                            Following up.

                            If you have further questions, please reply here.

                            If the post helped to solve your issue, please mark my answer as CORRECT / HELPED  to close the thread, not from inbox but from original post.

                             

                             

                            Thanks,

                            Shin

                            1 of 1 people found this helpful