5 Replies Latest reply on Jun 11, 2018 1:33 AM by David Maning

    Select second lowest quote

    Jose Garza Mayrl

      Hello,

       

      I have a data set that contains rows for quoted services. If there are 2 or more quotes then a new row is created in the data set. For example if I have 4 quotes for a service, I get:

       

      QuoteIDQuoteValue
      451700
      452000
      451400
      452200

       

      What I want to do is the following, if there is only 1 quote for a service I want to use that one, if there are 2 quotes then I want to use the maximum, but if there are 3 or more quotes then I want to always select the "second smallest" so in this case I would want the formula to select 1700.

       

      I have done the following

       

      I created a Calculated Field, which I named "NumberOfQuotes" that counts the number of quotes per service:

      COUNT([QuoteID])

       

      Then I created another calculated field with the following formula:

      IF [NumberOfQuotes] = 2

      THEN MAX([QuoteValue])

      ELSEIF [NumberOfQuotes] = 1

      THEN MAX([QuoteValue])

      END

       

      But I am stuck on how to make the formula select the second lowest in case there are 3 or more quotes.

       

      Any help would be greatly appreciated thanks,

       

      Jose

        • 1. Re: Select second lowest quote
          Jeevan Krishna

          Hi Jose,

           

          I am not sure if there is a cleaner solution.But I have 1 which can fix it for now.

           

          Use the following calculated fields

           

          This is what helps you create a filter that removes the max values from all the quotes where there are more than 3 amounts.

           

          For the below data,

           

          This is the result I got.

           

          Hope this helps.

          1 of 1 people found this helpful
          • 2. Re: Select second lowest quote
            garth.conrad

            Create a calculated field using rank([QuoteValue], desc) then select the item ranked number 2.

             

            or alternative, if rank([QuoteValue], desc) = 2 then “Choice” end.

             

            g

            • 3. Re: Select second lowest quote
              Jose Garza Mayrl

              This was very clean thank you so much.

              • 4. Re: Select second lowest quote
                Deepak Rai

                There is another way to Get What you were asking For. Please havea look Below. It would Return you Second Lowest Quote of 1700 Incase of Quote>=3 . Iam late in posing, but I Could not Resist Posting as If you won't need this, somebody in your situation would find the outcome useful.

                Thanks

                Deepak

                 

                This is Calculation 1

                • 5. Re: Select second lowest quote
                  David Maning

                  Hi Jose,

                   

                  You asked interesting question. I've got a bit different solution. It will help to get the exact figures you've asked in different cases.Sure that it's gonna be useful for you and other users. 

                   

                  So the main issue here is to get the second smallest value.

                  1) Hide Min and Max values of group (in your example it would be quote id).

                  IF

                  { FIXED [Sub-Category]:SUM([Sales])} <

                  { EXCLUDE [Sub-Category]:MAX({ FIXED [Sub-Category]:SUM([Sales])})}

                  AND

                  { FIXED [Sub-Category]:SUM([Sales])}>

                  { EXCLUDE [Sub-Category]:MIN({ FIXED [Sub-Category]:SUM([Sales])})}

                  THEN 1

                  ELSE 0

                  END

                   

                  2) We define our second smallest value.

                  {FIXED [Category]:PERCENTILE(

                      IF [Hide MIN & MAX Values] = 1

                      THEN { FIXED [Category],[Sub-Category]:SUM([Sales])}

                      END, 0)}

                   

                  3) We select value for each case.

                  IF { FIXED [Category]:COUNTD([Sub-Category])} = 2

                  THEN { EXCLUDE [Sub-Category]:MAX({ FIXED [Sub-Category]:SUM([Sales])})}

                  ELSEIF { FIXED [Category]:COUNTD([Sub-Category])} > 2

                  THEN [2nd Samllest Value]

                  ELSE { FIXED [Category], [Sub-Category]:SUM([Sales])}

                  END

                   

                  Done.

                   

                  Please find worksheet attached.

                   

                  Trust this helps.

                  D

                  1 of 1 people found this helpful