Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Like this?
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))
May be like attached qvw? Use dimension limit and sort order trick to make it work.
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.
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.
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.
Like this?
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.
Thanks Dan for your advise.I will make a note of input box as advised.
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.