3 Replies Latest reply on Dec 11, 2015 8:30 AM by Elliott Stam

    Territory Mapping and Windows_Sum function

    Diane Nugent

      I am using a technique I found on the forum to show my territory in a map (defined at the zip code level).  Each territory shows in a different color.  But now I want my totals to show up in tooltips.  I am struggling with the aggregation functions.  See the attached workbook which shows the map and has a table on the 2nd worksheet with the values that I want to include in the map tooltip.

       

      Any insight on using aggregation, especially windows_agg functions is greatly appreciated!

        • 1. Re: Territory Mapping and Windows_Sum function
          Elliott Stam

          Hi Diane,

           

          I tried to open the workbook but was prompted to enter credentials to the server you were using to access the SQL server. That boils down to the real problem - I can't view the worksheet. Could you first create a data extract before posting the file? Or if you make a mockup of what you are doing using sample data, I may be able to help you find a solution.

           

          Elliott Stam

          InterWorks

          • 2. Re: Territory Mapping and Windows_Sum function
            Diane Nugent

            Sorry about that!  Attached is the packaged workbook containing extract.  I also got the right result after many attempts, but do not fully understand how aggregation works.

             

            Can anyone point me to a good reference on Window_ agg functions that provide a good overall understanding?  Or, any improvements to my attached workbook are also welcome.

            • 3. Re: Territory Mapping and Windows_Sum function
              Elliott Stam

              Hi Diane,

               

              It looks like a LOD (level of detail) issue. I computed the same result you had in "Territory Measure" without using the window function by doing the following formula:

               

              {FIXED [Territory]:sum([Measure (copy)])}

               

              What this calculation is saying is, "Let's fix the calculation on the right side of the equation to the variable on the left side." So I am taking into consideration the entire territory when computing that sum, but not taking into consideration the pieces outside of that territory. LOD calculations allow you to fix a calculation to a certain value, include extra detail that the view does not show, or exclude detail that you do not want to have (fixed, include, and exclude are the three options in a LOD calculation).

               

              As for those window functions, those can be interpreted as performing a calculation based on what is currently being shown in your view. So if I have a different column for each state and calculated the window average, the result would be the average of all the values shown for each state. However, if your view changes and you are now looking at a different bar for each city, then the window average will automatically adjust to show the average for each city in the view.

               

              I think you managed a clever reproduction of what the LOD functions are meant to do by using the window sum, but in the future you could most likely achieve those calculations much more easily by letting the LOD functionality do the heavy lifting for you!

               

              Hope this helps.

               

              https://www.interworks.com/about-us/people/elliott-stam

              Elliott Stam

              InterWorks