3 Replies Latest reply on Feb 9, 2014 7:56 PM by Aaron Clancy

    Corresponding values from maximum in the previus column

    piero giansanti

      Hi all,


      I am currently working with a data set like this




      What I would like to do is to aggregate the data on column 1 and take the corresponding maximum  in column 2.



      This is easy, ok! However, at the same time, I would like to obtain from column 3 the value corresponding to the max in column 2, that is



      How can I do this?


      Thank you in advance for your help.

        • 1. Re: Corresponding values from maximum in the previus column
          Aaron Clancy

          In order for this to work I added a unique identifier to your data (which I would recommend as a good best practice for many data storage scenarios)

          Screen Shot 2014-02-08 at 11.09.40 AM.png

          Then I created a filter calc:

          Screen Shot 2014-02-08 at 11.10.37 AM.png


          I set up the columns and rows like the following:

          Screen Shot 2014-02-08 at 11.11.26 AM.png

          (All Discrete fields)


          Then I placed the filter on the filters shelf with the following compute using configuration:

          Screen Shot 2014-02-08 at 11.12.29 AM.png

          Screen Shot 2014-02-08 at 11.13.09 AM.png


          Set the filter to "Yes"

          Then uncheck "Show Header" for the uniqueID field.


          The result should be:

          Screen Shot 2014-02-08 at 11.13.43 AM.png

          • 2. Re: Corresponding values from maximum in the previus column
            Joshua Milligan



            Aaron's approach definitely works.  Here's a variation which avoids adding a unique identifier (although I agree with Aaron that it is often a good practice).

            Create a single calculated field that evaluates whether the SUM of the value is the same as the WINDOW_MAX.  This is very similar to Aaron's first step.



            When you place it in the view like this, and set it to comput using Type (right click [Is Highest] on Rows and select "Compute Using" --> "Type"), then you'll get a True value for each aggregate row you want to keep.



            Simply drag the [Is Highest] field from Rows to Filters and keep the True values.  This is the result:






            • 3. Re: Corresponding values from maximum in the previus column
              Aaron Clancy

              I agree with the approach.

              The only caveat I would include is that:

              If an ID can have multiple instances of the same "type" then they're going to get rolled in together as opposed to individual records.

              If this never happens in the data then it doesn't matter.

              On that note, if it never happens then ID+TYPE = composite unique ID and could be considered a unique identifier that already exists in the data.


              Sent from my iPhone