Setting a range (min and max)

[Triss]

Hi all,

I'm hoping someone can give me some insight into how to create a range that has a min and max value.

I have a dataset akin to the following: 

Which when summed looks like this:

I am trying to generate the 'sum(BBServ) / sum(Serv)' column where values over 100% are rounded to 100% and values that are negative are either rounded to 0% or made null.

I have the following code that deals with the negatives, but I'm struggling to figure out how to also round the figures over 100%.

'rangemax(sum(BBServ),0)/rangemax(sum(Serv),0)'

This code generates the following:

I'd be very grateful for any assistance.

Thanks!

[Gabbar]

Try using if statement:-
If(sum(BBServ) / sum(Serv)<0,0,If(sum(BBServ) / sum(Serv)>1,1,sum(BBServ) / sum(Serv)))

[Aditya_Chitale]

Try this:

if((Sum(BBServ)/sum(Serv)) < 0 or (RANGEMAX(sum(BBServ),0)/rangemax(sum(Serv),0)) = null() , RANGEMAX(sum(BBServ),0)/rangemax(sum(Serv),0) ,
if( (Sum(BBServ)/sum(Serv)) > 1 , 1 , RANGEMAX(sum(BBServ),0)/rangemax(sum(Serv),0)))

output:

Regards,

Aditya

[Triss]

Thanks Gabbar. I forgot to include this in my original post, but I'm hoping to avoid using an if statement as it tends to slow things down considerably.

[Gabbar]

 Try this:-
Rangemin(Rangemax(Rangemax(Sum(BBserv),0)/Rangemax(Sum(Serv),0),0),1)

[Triss]

Pretty close, but it seems to not like the double negatives:

[Gabbar]

Yes because you said that you made the code the solves values in negative and also showed making them null is ok, thats why i went with that,
otherwise this might work:-

Rangemin(Rangemax(Sum(BBserv)/Sum(Serv),0),1)