topic Re: Rank with dynamic sorting in QlikView

Rank with dynamic sorting

aj0031724 — Fri, 09 Dec 2016 08:48:43 GMT

Dear team,

Can you please advise how to achieve RANK output as below with dynamic sorting of KPI as shown

KPI	SORTORDER	formula
Drop Rates %	Desc	sum(count)/5
callcount	Asc	sum(count)

Master Table with KPI
id	name	Drop Rates %	callcount
1	A	5	5
2	B	5	5
3	C	10	10
4	D	10	10
5	E	2	2
6	F	3	3
7	G	7	7
8	H	1	1
9	I	4	4
10	J	4	4
11	k	4	4

OUTPUT REUIRED IN STARIGHT TABLE to DISPLAY 5 ROWS:

Id	name	Drop Rate%	Rank
3	C	10	1,2
4	D	10	1,2
7	G	7	3
1	A	5	4,5
2	B	5	4,5

ID	name	Calls Count	Rank
8	H	1	1
5	E	2	2
6	F	3	3
9	I	4	4,6
10	J	4	4,6

Re: Rank with dynamic sorting

MK_QSL — Fri, 09 Dec 2016 09:00:58 GMT

Dimension

=Aggr(IF(Rank(sum(callcount),4)<=5,id),id)

Tick Suppress WHen Value is Null

name

Expression

Rank(TOTAL sum(callcount))

Dimension

=Aggr(IF(Rank(-sum(callcount),4)<=5,id),id)

Tick Suppress WHen Value is Null

name

Expression

Rank(TOTAL -sum(callcount))

Re: Rank with dynamic sorting

tresB — Fri, 09 Dec 2016 09:15:16 GMT

May be like attached qvw? Use dimension limit and sort order trick to make it work.

Re: Rank with dynamic sorting

aj0031724 — Fri, 09 Dec 2016 09:42:49 GMT

Dear All ,

We have only one straight table that need to be changed accordingly with change in KPINAME selection.

So all sort order and number of rows to display should be changed dynamically.

Also formula is different as shown for DropRate% and CallCount.

Here I have shown for N=5. Number of Rows to display is an input box tht user can select between 5-100.

Is there any way by which this can be adjusted dynamicaaly to providesame output.

Re: Rank with dynamic sorting

aj0031724 — Fri, 09 Dec 2016 09:43:09 GMT

Dear All ,

We have only one straight table that need to be changed accordingly with change in KPINAME selection.

So all sort order and number of rows to display should be changed dynamically.

Also formula is different as shown for DropRate% and CallCount.

Here I have shown for N=5. Number of Rows to display is an input box tht user can select between 5-100.

Is there any way by which this can be adjusted dynamicaaly to providesame output.

Re: Rank with dynamic sorting

danvalensi — Fri, 09 Dec 2016 10:15:54 GMT

Hi,

To have the number of rows dynamically change, you have to use a variable and put it in the Dimension Limit Tab

When you have something like that, users can change values by an :

- an input box (classic)

- a slider much more sexy 🙂

you can configure it like this. Make sure the static step field have an integer value. If not, you will have a weird behavior.

Hope this help !

Dan.

Re: Rank with dynamic sorting

tresB — Fri, 09 Dec 2016 10:50:17 GMT

Like this?

Re: Rank with dynamic sorting

aj0031724 — Fri, 09 Dec 2016 11:37:06 GMT

Dear All,

Thanks a lot for your excellent guidance and advise to get the required output .

I will implement the same logic in production and see how it behaves.

One thing that wanted to know is th eexpression logic:

dual(sum(count),-sum(count))??How this gets sorted in ascending order for Callcount?

Thanks again.

Re: Rank with dynamic sorting

aj0031724 — Fri, 09 Dec 2016 11:38:07 GMT

Thanks Dan for your advise.I will make a note of input box as advised.

Re: Rank with dynamic sorting

tresB — Fri, 09 Dec 2016 11:46:49 GMT

dual(sum(count),-sum(count)) - this is basically showing a negative value as positive (using dual()). -sum(count) is being used to bring an inverse ranking (i.e. - bottom to top). If you notice, I limited the dimension based on 'largest' values, however, for that expression I wanted a ranking on 'smallest' values. The negative ranking does the trick here.

Hope this helps.

Re: Rank with dynamic sorting

aj0031724 — Mon, 12 Dec 2016 13:15:01 GMT

Dear Tresesco,

One diffrenece I noted. One thing that the requirement output was that for CALLCOUNT Rank output is hsowing 5 records but it ranked till4-6 because next ID also has same value.

As per the output requirement same values should be grouped but numbe rof records to display is controlled by vrank.

Can I get the output as shown above for CALLCOUNT.

	name	Calls Count	Rank
8	H	1	1
5	E	2	2
6	F	3	3
9	I	4	4,6
10	J	4	4,6