topic Re: Top and Bottom Values from Expression in QlikView

Top and Bottom Values from Expression

lierac200 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All

I have a few questions:

1) Is it possible to have Top 3 and Bottom 3 PersonIDs based on sum(Value) in text Box? Top: 6,5,7, Bottom: 8,2,4

2) Is it possible to have PersonIDs that are between Top3 and Bottom3? E.g. 1,3,8,9

3) Is is possible to have a listbox with values: 'Top3', 'Between Top3 and Bottom3' , 'Bottom3'? And if we click on Top3 then PersonIDs 6,5,7 will be selected for example? But not to create this by adding new field to the data model, but only in the front-end to be 'dynamic'? So top3 from selected values?

Qvw attached. Screenshot below:

Thank you for help

Re: Top and Bottom Values from Expression

sunny_talwar — Mon, 14 May 2018 15:06:01 GMT

May be these

Top 3

=Concat(DISTINCT {<PersonID = {"=Rank(Sum(Value)) < 4"}>}PersonID, ', ')

Between Top 3 and Bottom 3

=Concat(DISTINCT {<PersonID = {"=Rank(Sum(Value)) > 3 and Rank(-Sum(Value)) > 3"}>}PersonID, ', ')

Bottom 3

=Concat(DISTINCT {<PersonID = {"=Rank(-Sum(Value)) < 4"}>}PersonID, ', ')

Re: Top and Bottom Values from Expression

eduardo_dimperio — Mon, 14 May 2018 15:10:41 GMT

You can do this on script:

Order by asc SUM(VALUE) and use Peek(field_name[, row_no[, table_name ] ])

where

PERSON_ID> Peek(PERSON_ID,3, YOUR_TABLE)

where

PERSON_ID< Peek(PERSON_ID,-3, YOUR_TABLE)

where

PERSON_ID> Peek(PERSON_ID,3, YOUR_TABLE) AND PERSON_ID< Peek(PERSON_ID,-3, YOUR_TABLE)

Re: Top and Bottom Values from Expression

sunny_talwar — Mon, 14 May 2018 15:10:47 GMT

Try this for list box

=Aggr(

If(Rank(Sum({1}Value)) < 4, Dual('Top 3', 1),

If(Rank(-Sum({1}Value)) < 4, Dual('Bottom 3', 3), Dual('Between Top 3 and Bottom 3', 2)))

, PersonID)