8 Replies Latest reply on Mar 2, 2017 9:17 AM by Mike Labman

    Regional Sales Leader by average

    Mike Labman

      I have a case where I need to identify a sort of "best of region" metric.  I've come up with a very simplified example.  The source data would be like this:

       

       

      Salesperson

      Date

      Quantity

      Total

      Region

      John

      1/1/2017

      3

      1000

      East

      Susan

      1/2/2017

      1

      1000

      East

      Susan

      1/3/2017

      1

      1000

      East

      John

      1/4/2017

      10

      100

      West

      John

      1/5/2017

      3

      100

      West

      John

      1/6/2017

      2

      1000

      West

      Susan

      1/7/2017

      5

      45

      West

      Susan

      1/8/2017

      5

      45

      West

       

       

      To this data, I've added a calculated field called "AvgSalesPrice" which is defined as SUM([Total])/SUM([Quantity]).

       

      If I then plot this on a sheet with Region in Columns and salesperson in Rows,  I get a nice simple table like this:

       

       

      Region
      SalespersonEastWest
      John333.380
      Susan1,0009

       

       

      From here, what I need is a single row that identifies Susan as the East winner and John as the West winner, i.e.

       

       

      Region
      EastWest
      WinnerSusanJohn

       

      Not sure if I'm approaching this incorrectly.  It seems like a really basic feature, but I can't seem to find how to do it.

       

      Message was edited to include a 10.1 example.

        • 1. Re: Regional Sales Leader by average
          Joe Oppelt

          I get an error opening your workbook.  Are you on 10.2 already?

           

          So with your setup as it is, you can do a WINDOW_MAX on those numbers and set the table calc settings so that it restarts every [Region].  Then you can do a tale calc filter that says

           

           

          IF <salesman's average> = <window_max calc> then 1 else 0 END

           

          And put that on the filter shelf and select for value = 1.

           

          You can also do this with LODs.


          I was going to show you both, but I can't open the file.

          • 2. Re: Regional Sales Leader by average
            Mike Labman

            Joe,

             

            Thanks for fast response.  Yes, I just upgraded to 10.2 because I've been patiently waiting for the Spatial File connector for months!  I'll give it a shot with window_max and if I run into issues, I'll re-attach a 10.1 sample.

             

            Mike

            • 3. Re: Regional Sales Leader by average
              Mike Labman

              Joe,

              I added a SalesMax which uses the WINDOW_MAX function and I've used this to compare to each AvgSalesPrice, which has given me a 0 or a 1, which is correct - but still not sure how to get the actual value of "John" or "Susan" to show up.   See attached 10.1 version of the workbook.

              • 4. Re: Regional Sales Leader by average
                Joe Oppelt

                See attached.

                 

                Sheets 2-4 use table calcs.  Sheets 5-7 use LOD.

                 

                On Sheet 2 I collect the winning name.  But it only shows up in the actual row that name lives on.

                 

                On Sheet 3 I made a copy of the Display Winner calc so that it displays on all rows.  (You don't need multiple copies of the calc, of course.  I just left breadcrumbs as I moved along here.)

                 

                On sheet 4 I moved most of the TEXT fields to Detail.  For the sake of the exercise, remove INDEX from the filter shelf.  You'll see that we have multiple copies of the value, one per salesperson row.  That's how table calcs work.  They get evaluated for each cell.  I also put Salesperson on Detail rather than ROWS shelf.


                Hit backarrow to put INDEX back.  Because the value is the same for each row, I just need to show one row.  INDEX is set to grab just the first one.

                 

                Look at the table calc settings.  I have it evaluate across all dimensions, restarting every [Region] in this case.  That setting is on every table calc in play here.

                 

                That's the table calc way.


                Now go to Sheet 5.  Here I use LODs.

                 

                First I use LOD to calc the average per salesperson per region.

                 

                On sheet 6 I add the LOD to find the max per region.

                 

                On sheet 7 I can completely throw Salesperson off the sheet.  That's because the LODs do what they need to do to see Salesperson.

                1 of 1 people found this helpful
                • 5. Re: Regional Sales Leader by average
                  Mike Labman

                  Joe,

                   

                  I appreciate the thorough walk-through.  I like the LOD approach and I think it will work great for my application of this logic.  It's more complex but I think this will get me on the right path.

                  • 6. Re: Regional Sales Leader by average
                    Mike Labman

                    Joe,

                     

                    I've got one more question.  Now, I've added a "Category" column into the base table which I have added as a filter.  I am not interested in seeing a per-category breakdown visually - I only want to see one winner in the Viz, but I want to be able to pick 1 or more categories.

                     

                    To get the category-level detail, I've added [category] to the LODs as:

                     

                    { fixed [Salesperson],[Region],[Category] : SUM([Total]) } /

                    { fixed [Salesperson],[Region],[Category] : SUM([Quantity]) }

                     

                    and

                     

                    {FIXED [Region],[Category] : MAX([LOD AvgSalesPrice])}

                     

                    So, this works fine when I only have a single category picked.  But when I pick both categories,  now it displays 2 different regional winners.

                     

                    Attaching

                    • 7. Re: Regional Sales Leader by average
                      Joe Oppelt

                      By placing all three dimensions in the LODs, you create a value per-salesperson/per-region/per-category.  Therefore when you have two categories selected, you will get two values displayed.

                       

                      If you want the winner to be calc'd for the sum of all categories, then take off [Category] from the LODs.  However there is a catch.  a FIXED LOD evaluates before filters are applied, under normal circumstances.  Therefore I think you are seeing that if you select only one Category, your LOD calc doesn't change.

                       

                      There are two ways to address this.  In addition to FIXED, we have INCLUDE and EXCLUDE.  These evaluate after filters are applied.  These act a little different (at least in my way of looking at things ), and I prefer the second approach...

                       

                      Take[Category] off the LODs, and then in your sheet right click on the [Category] pill on the filter shelf.  Select "Add to Context".  This changes the behavior of the filter to act BEFORE the FIXED LOD evaluates.  Now, you will still get numbers per-salesperson/per-region, and the numbers will only include the rows selected by the Category filter.

                       

                      So depending on what you are actually looking for, there are ways to address the behavior you are currently seeing.

                       

                      Keep in mind that if you ever want to see a winner per category (and especially if you want to display these winners across multiple categories), your LOD will have to include [Category] in the list of dimensions.  If you come across a requirement where on one sheet you need to display it one way and in another sheet display it the other, you'll maintain multiple sets of LOD to handle the different conditions.

                      1 of 1 people found this helpful
                      • 8. Re: Regional Sales Leader by average
                        Mike Labman

                        Beautiful and simple - the "Add to Context" did exactly what I wanted.