Top 3 from the list in TextBox qliksense

[sowmi]

Hi Everyone,

I'm trying to show top 3 from the list

for example 

Field ,Value

a - -9

b--8

c--6

d- 1

Expecting O/p in Text box as

Text box with top 3 should take the top 3 positive values,If there are no positive values anything should be dispalyed,As from the above data my top 3 will show a:1

For Bottom 3 it should always consider the top negative values

a - -9

b--8

c--6

Please share your views

Thanks

Sowmya

[sunny_talwar]

What are you looking to get? I guess what is wrong with the output?

[sowmi]

I'm expecting o/p like above image ,but with the expression i postes it is giving me like below 

this is my data

[sunny_talwar]

Try this... hopefully I got all the parenthesis right 🙂

=Concat(Aggr(
	If(Rank(TOTAL (Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value]))) <= 3,
		[Material Brand]
		& ' | ' &
		(Sum({<Year={'2019'}>}value)-Sum({<Year={'2018'}>}value))
		& Chr(10) &
		(Rank(TOTAL (Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value]))))
	)
, [Material Brand])) 

[sowmi]

It is not sorting 

 getting o/p

[sunny_talwar · Accepted solution]

May be try this

=Concat(Aggr(
	If(Rank(TOTAL (Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value]))) <= 3,
		[Material Brand]
		& ' | ' &
		(Sum({<Year={'2019'}>}value)-Sum({<Year={'2018'}>}value))
		& Chr(10) &
		(Rank(TOTAL (Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value]))))
	)
, [Material Brand]), Aggr(Rank(TOTAL (Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value]))), [Material Brand])) 

or this

=Concat(Aggr(
	If(Rank(TOTAL (Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value]))) <= 3,
		[Material Brand]
		& ' | ' &
		(Sum({<Year={'2019'}>}value)-Sum({<Year={'2018'}>}value))
		& Chr(10) &
		(Rank(TOTAL (Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value]))))
	)
, [Material Brand]), -Aggr(Rank(TOTAL (Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value]))), [Material Brand])) 

[sowmi]

for the Below expression i am trying to give number format when the value is greater than 1000 this works fine ,How to give format when the value is<=100

Concat(If(Aggr(Rank(TOTAL Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value])),
[Material Brand]) <=3, [Material Brand] & ' | ' & Replace(num(Aggr(Sum({<Year={'2019'}>}value)-Sum({<Year={'2018'}>}value), [Material Brand])/1000,'# ##0K','.',' '),' ',',')), 
Chr(10), Aggr(Rank(TOTAL Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2019}>} [value]) - Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value) - Sum({<Year = {2018}>} value)) > 0"}, Year = {2018}>} [value])), [Material Brand]))

[sunny_talwar]

What are the two formats you are looking to get?

[sowmi]

If my value is >1000 it should show something like 23k,if <=100 show 230 

[sunny_talwar]

Expression is already fairly complex, I don't think I will be able to give you a working expression without testing it out... but you can check for the idea from here -> Format Number (in Thousand)