Solved: Top 3 from the list in TextBox qliksense - Qlik Community

sowmi · ‎2019-10-25

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 · ‎2019-10-30

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

Channa · ‎2019-10-25

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

it will bring top 3 names

ur measure =Sum(value)

Channa

sowmi · ‎2019-10-25

I dont think it will through the expecting output

Channa · ‎2019-10-25

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

Just do this

Channa

JGMDataAnalysis · ‎2019-10-25

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 · ‎2019-10-28

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 · ‎2019-10-28

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 · ‎2019-10-29

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 · ‎2019-10-29

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 · ‎2019-10-29

getting below o/p which is not right

Top 3 from the list in TextBox qliksense

Client Managed

General Question