Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
sowmi
Creator
Creator

Top 3 from the list in TextBox qliksense

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

Labels (4)
1 Solution

Accepted Solutions
sunny_talwar

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])) 

View solution in original post

18 Replies
Channa
Specialist III
Specialist III

dimension =If(Aggr(Rank(Sum(value)),Name)<=3,Name)

 

it will bring top 3 names

ur measure =Sum(value)

Channa
sowmi
Creator
Creator
Author

I dont think it will through the expecting output

Channa
Specialist III
Specialist III

dimension =If(Aggr(Rank(-Sum(value)),Name)<=3,Name)

 

Just do this 

Channa
JGMDataAnalysis
Creator II
Creator II

Positive Values:

Concat(If(Aggr(Rank(TOTAL Sum({<[Field] = {"=Sum([Value]) > 0"}>} [Value])), [Field]) <= 3, [Field]), ', ', Aggr(Rank(TOTAL Sum({<[Field] = {"=Sum([Value]) > 0"}>} [Value])), [Field]))

Negative Values:

Concat(If(Aggr(Rank(TOTAL Sum({<[Field] = {"=Sum([Value]) < 0"}>} -[Value])), [Field]) <= 3, [Field]), ', ', Aggr(Rank(TOTAL Sum({<[Field] = {"=Sum([Value]) < 0"}>} -[Value])), [Field]))

 

sowmi
Creator
Creator
Author

 getting only field name need corresponding value to that field and want to display each field and value in new line ,if i have top 2 it should display in 2 lines 

 

 

JGMDataAnalysis
Creator II
Creator II

Something like this?

Positive Values:

Concat(If(Aggr(Rank(TOTAL Sum({<[Field] = {"=Sum([Value]) > 0"}>} [Value])), [Field]) <= 3, [Field] & ' | ' & Aggr(Sum([Value]), [Field])), Chr(10), Aggr(Rank(TOTAL Sum({<[Field] = {"=Sum([Value]) > 0"}>} [Value])), [Field]))

 Negative Values:

Concat(If(Aggr(Rank(TOTAL Sum({<[Field] = {"=Sum([Value]) < 0"}>} [Value])), [Field]) <= 3, [Field] & ' | ' & Aggr(Sum([Value]), [Field])), Chr(10), Aggr(Rank(TOTAL Sum({<[Field] = {"=Sum([Value]) < 0"}>} -[Value])), [Field]))
sowmi
Creator
Creator
Author

it is not working as expected ,i am writing down the expression like this :

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

 By using this expression it is not sorting the values ,I have attached the image of data 

 

 
 
sunny_talwar

Can you check this

=Concat(Aggr(
	If(Rank(TOTAL Sum({<[Material Brand] = {"=(Sum({<Year = {2019}>} value)- Sum({<Year = {2018}>} value)) > 0"}>} [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"}>} [value])))
	)
, [Material Brand])) 
sowmi
Creator
Creator
Author

getting below o/p which is not right

     2019-10-29_14-30-53.png