Experts,
Currently facing an issue with Ranking some values over 2 dimensions - COUNTRY and KPI_NAME
The user selects any 2 countries at a time , and the radar chart displays the rank of the Priority IDs of the KPI_NAMEs.
The Priority is defined in a way that it is sorted in a particular order - and the rank is displayed based on that order.
Only the common KPIs that are present for selected countries should be displayed - and that is handled via
Measure :
SUM({<KPI_NAME={$(=Concat(IF( Aggr(Count(DISTINCT COUNTRY_NAME),KPI_NAME) =2,chr(39)&KPI_NAME&chr(39)),','))}>}Scaled_Value)
Dimension :
1 )aggr(rank(only({<KPI_PRIORITY=>}-KPI_PRIORITY)),KPI_NAME)
2 ) COUNTRY_NAME
Issue :
The ranking should not change irrespective of selections on the KPI_PRIORITY_ID - have tried giving KPI_PRIORITY= in the dimension
This does not work well for all pairs of country selection.
Correct Output :
| COUNTRY_NAME | KPI_PRIORITY | aggr(rank(only({<KPI_PRIORITY=>}-KPI_PRIORITY)),KPI_NAME)
| round(SUM({<KPI_NAME= |
| SWEDEN | 1 | 1 | 4.000 |
| AUSTRALIA | 1 | - | 2.880 |
| SWEDEN | 7 | 2 | 2.300 |
| AUSTRALIA | 7 | - | 1.780 |
| SWEDEN | 9 | - | 2.650 |
| AUSTRALIA | 9 | 3 | 1.000 |
| SWEDEN | 10 | - | 1.540 |
| AUSTRALIA | 10 | 4 | 1.000 |
| SWEDEN | 15 | 5 | 2.430 |
| AUSTRALIA | 15 | - | 1.000 |
| SWEDEN | 18 | - | 2.800 |
| AUSTRALIA | 18 | 6 | 2.500 |
| SWEDEN | 19 | 7 | 4.000 |
| AUSTRALIA | 19 | - | 2.630 |
| AUSTRALIA | 20 | - | 2.420 |
| SWEDEN | 20 | 8 | 1.120 |
| SWEDEN | 22 | - | 4.000 |
| AUSTRALIA | 22 | 9 | 2.800 |
Wrong Output :
| COUNTRY_NAME | KPI_PRIORITY | aggr(rank(only({<KPI_PRIORITY=>}-KPI_PRIORITY)),KPI_PRIORITY) | round(SUM({<KPI_NAME=Scaled_Value),0.001) |
| JAPAN | 1 | 1 | 3.480 |
| ITALY | 1 | - | 2.460 |
| ITALY | 3 | 2 | 0.000 |
| ITALY | 7 | - | 2.340 |
| JAPAN | 7 | 3 | 1.170 |
| ITALY | 8 | 4 | 0.000 |
| ITALY | 9 | 5 | 2.270 |
| JAPAN | 9 | - | 1.140 |
| ITALY | 10 | 6 | 3.970 |
| JAPAN | 10 | - | 2.180 |
| ITALY | 11 | 7 | 0.000 |
| ITALY | 13 | 8 | 0.000 |
| ITALY | 14 | 9 | 0.000 |
| ITALY | 15 | 10 | 3.940 |
| JAPAN | 15 | - | 1.320 |
| ITALY | 16 | 11 | 0.000 |
| ITALY | 17 | 12 | 0.000 |
| ITALY | 18 | - | 2.000 |
| JAPAN | 18 | 13 | 2.000 |
| ITALY | 19 | 14 | 2.630 |
| JAPAN | 19 | - | 2.630 |
| JAPAN | 20 | - | 1.320 |
| ITALY | 20 | 15 | 1.120 |
| JAPAN | 22 | 16 | 4.000 |
| ITALY | 22 | - | 2.800 |
In the above result , rank 2 should not have come for KPI_PRIORITY 3 , as 3 is not common between the selected countries.
Kindly help to resolve this issue.
