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

# Select second lowest quote

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

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

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

This was very clean thank you so much.

• ###### 4. Re: Select second lowest quote

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

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.