Rank with dynamic sorting

[aj0031724]

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

[MK_QSL]

1)

Dimension

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

Tick Suppress WHen Value is Null

name

Expression

Rank(TOTAL sum(callcount))

2)

Dimension

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

Tick Suppress WHen Value is Null

name

Expression

Rank(TOTAL -sum(callcount))

[tresesco]

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

[aj0031724]

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.

[aj0031724]

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.

[danvalensi]

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.

[tresesco · Accepted solution]

Like this?

[aj0031724]

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.

[aj0031724]

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

[tresesco]

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.