To pick required Value based on multiple conditions

[hitha1512]

Hi Team,

Below is sample data I have and I need to calculate the best value based on few condition as below.

1. Check the Minimum rank, if there are more than one row\records, then

2. Check the minimum Tier value, if there are more than one row\records, then

3. Check the minimum date and consider that as Best Value

But Best value should be based on above condition and precedence only, First it has to check the rank, then tier value and then date.

Contract	Rank	Tier Value	Date
Premier Inc	9	42805	8/05/2019
MedAssets	9	30000	3/24/2019
Vizient	9	61150	8/05/2019
Cardinal Health	8	42805	8/23/2019
HealthTrust Purchasing Group	8	42805	8/05/2019

Result :

Contract	Rank	Tier Value	Date	Best Value
Premier Inc	9	42805	8/05/2019
MedAssets	9	30000	3/24/2019
Vizient	9	61150	8/05/2019
Cardinal Health	8	42805	8/23/2019
HealthTrust Purchasing Group	8	42805	8/05/2019	TRUE

Any help is appreciated!

Regards,

Hitha Dhani

[Deepak87]

Hi @sunny_talwar ,

How to do it in front end?

also the tier value are 5 digit now. but if the tier value field length is not constant means, how to do it?

Regards,

Deepak

[sunny_talwar · Accepted solution]

You can try this using Rank() function on top of -(Rank + [Tier Value]/1e5 + Date/1e10) to determine  rank 1... Rank 1 will be your lowest value.

Also, I would need to see an example for what you mean by Tier Value. This is kind of dependent on each value for each section because I am trying to weight them a little differently. But based on possible range of values, we can determine the weights.

[Deepak87]

Hi @sunny_talwar 

Tier Value is an amount. can range from 0 to max a million

[sunny_talwar · Accepted solution]

Sorry, but are you working on a similar issue or do you work with @hitha1512 ? I just want to make sure I understand how your issue is related to the one in question.

In that case, may be create value like this

Rank*1e10 + [Tier Value]/1e5 + Date/1e10

Multiplied Rank by 10,000,000,000 to make sure Tier Value doesn't end up exceeding Rank (hence adding more weight to Rank).

Do you get the idea? It is all about giving more weight to the field which is first in order of decision making and less weight to the field which is last in order of decision making.

[Deepak87]

Hi @sunny_talwar ,

Kind of  me and @hitha1512  are working on same issue.

I tried your solution and it's seems to be working.

Thanks a lot.

[sunny_talwar]

Okay that is great. @hitha1512 is your issue resolved as well? If it is, then please close this thread.

[hitha1512]

@sunny_talwar  me and Deepak were working on the same issue.

thanks a ton 🙂  much appreciated 🙂