Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
aj0031724
Partner
Partner

Rank with dynamic sorting

Dear team,

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

 

KPISORTORDERformula
Drop Rates %Descsum(count)/5
callcountAscsum(count)

   

Master Table with KPI
idnameDrop Rates %callcount
1A55
2B55
3C1010
4D1010
5E22
6F33
7G77
8H11
9I44
10J44
11k44

OUTPUT REUIRED IN STARIGHT TABLE to DISPLAY 5 ROWS:

   

IdnameDrop Rate%Rank
3C101,2
4D101,2
7G73
1A54,5
2B54,5

   

IDnameCalls CountRank
8H11
5E22
6F33
9I44,6
10J44,6
1 Solution

Accepted Solutions
tresesco
MVP
MVP

10 Replies
MK_QSL
MVP
MVP

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
MVP
MVP

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

aj0031724
Partner
Partner

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
Partner
Partner

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
Partner
Partner

Hi,

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

Capture_help.JPG

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.Capture_help_2.JPG

Hope this help !

Dan.

tresesco
MVP
MVP

aj0031724
Partner
Partner

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
Partner
Partner

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

tresesco
MVP
MVP

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.