100 th next value

[#1]

Hi

I have values like below

2011  98

2012  100

2013  108

2014   77

2015   109

2016  234

i want to get the 100 value ?

if

2011  98

2012  103

2013  108

2014   77

2015   109

2016  234

i want to get 103 here?

want to show if value =100 or greater then 100 first value

means always one value that is 100 or next 100th value

i am using

Rank(-Fabs(Sum(Sales)-100)) it is not taking always 100 some times 98 or 99 also it is taking

please do the needful

Thiru

[effinty2112]

Hi Thiru,

=Concat(DISTINCT if(aggr(Rank(if(Value<100, Null(),-fabs(Value -100))),Year)=1,Value))

will return the value you want.

=Concat(if(aggr(Rank(if(Value<100, Null(),-fabs(Value -100))),Year)=1,Year),', ')

will return the year (or years separated by a comma) that had the value you're looking for.

Cheers

Andrew

[#3]

HI Andrew,

It's not working

Please do the needful

Thiru

[MarcoWedel]

Hi,

maybe like this:

hope this helps

regards

Marco

[#5]

Hi Marco,

Thanks for your reply.It is working fine when we are using a  column but mine is expression

like below

min({$<column(2)={">=100"}>}column(2))

It is not working

Thiru

[#6]

Hi Marco,

Please find the attached qvw and excel

i want show 100 th or greater then 100 value as symbol.

What you have done is directly from value but my case it is formula.

sales+sales_impact(input column)  based on that i need to show the 100 value

Crashdate:

if((if(Rank(-Fabs(column(2)-100))=1,column(2),0))>=(100-10),column(2)-(column(2)-100))

please do the needful

Thiru