3 Replies Latest reply on Feb 10, 2014 8:29 AM by Matt Lutton

    maximum values and associated fields

    Derek Noble

      Hi Guys,


      been trying to think of a way to do this but my mind has no turned to mush.

      What I want to do might be simple, well at least I am hoping so.

      I am trying to get a list of maximum values for different measures but also include the dimensions that describe which entries these measures come from.

      For example, lets say I want the maximum sales for all regions but I also want to display on this list the sales-person, their location and phone number. I dont want to have a list of all sales-people, just the max for a period and the sales-persons details.


      thanks in advance.



        • 1. Re: maximum values and associated fields
          Matt Lutton

          If you can post a packaged workbook (.twbx) example along with a mockup of the end result you're looking for, I'm sure someone would be happy to help.  Its very difficult to describe a process for your problem without seeing the data and interacting with a workbook. 

          • 2. Re: maximum values and associated fields
            Derek Noble

            Good idea Matthew.

            I have attached a small workbook with test data that explains what I want to do.

            for each date I want to display ONLY the max sales figure and the name and region of the salesman who was responsible.

            • 3. Re: Re: maximum values and associated fields
              Matt Lutton


              Here's one possible solution (there are several ways you could approach this).


              1) Created a calculated field: IF FIRST()==0 THEN WINDOW_MAX(Sum(Sales)) calc END


              With the proper compute using settings (addressing on Date>Name>Region in that order), this will result in returning the maximum value per each date/name/region combination.  (see attached screenshot)


              This leaves NULL values in the rest of the rows--so we can then create a filter like:


              NOT ISNULL([WINDOW_MAX Sales])


              And place that on the Filter shelf, set to "True".  The result should be the dates with the corresponding name, region, and corresponding maximum sales value.