topic 100 th next value in QlikView

100 th next value

Wed, 25 Nov 2020 16:16:04 GMT

I have values like below

2011 98

2012 100

2013 108

2014 77

2015 109

2016 234

i want to get the 100 value ?

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

Re: 100 th next value

effinty2112 — Mon, 08 Aug 2016 10:47:01 GMT

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

Re: 100 th next value

Mon, 08 Aug 2016 11:30:48 GMT

HI Andrew,

It's not working

Please do the needful

Thiru

Re: 100 th next value

MarcoWedel — Tue, 09 Aug 2016 00:01:29 GMT

Hi,

maybe like this:

hope this helps

regards

Marco

Re: 100 th next value

Wed, 10 Aug 2016 06:36:11 GMT

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

Re: 100 th next value

Wed, 10 Aug 2016 07:19:49 GMT

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