Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

samsunqv
New Contributor II

Issue with ignoring selections for ranking

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_NAMEKPI_PRIORITYaggr(rank(only({<KPI_PRIORITY=>}-KPI_PRIORITY)),KPI_NAME)

round(SUM({<KPI_NAME=
SWEDEN114.000
AUSTRALIA1-2.880
SWEDEN722.300
AUSTRALIA7-1.780
SWEDEN9-2.650
AUSTRALIA931.000
SWEDEN10-1.540
AUSTRALIA1041.000
SWEDEN1552.430
AUSTRALIA15-1.000
SWEDEN18-2.800
AUSTRALIA1862.500
SWEDEN1974.000
AUSTRALIA19-2.630
AUSTRALIA20-2.420
SWEDEN2081.120
SWEDEN22-4.000
AUSTRALIA2292.800

Wrong Output :

 

COUNTRY_NAMEKPI_PRIORITYaggr(rank(only({<KPI_PRIORITY=>}-KPI_PRIORITY)),KPI_PRIORITY)round(SUM({<KPI_NAME=Scaled_Value),0.001)
JAPAN113.480
ITALY1-2.460
ITALY320.000
ITALY7-2.340
JAPAN731.170
ITALY840.000
ITALY952.270
JAPAN9-1.140
ITALY1063.970
JAPAN10-2.180
ITALY1170.000
ITALY1380.000
ITALY1490.000
ITALY15103.940
JAPAN15-1.320
ITALY16110.000
ITALY17120.000
ITALY18-2.000
JAPAN18132.000
ITALY19142.630
JAPAN19-2.630
JAPAN20-1.320
ITALY20151.120
JAPAN22164.000
ITALY22-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.

3 Replies
MVP
MVP

Re: Issue with ignoring selections for ranking

It might be helpful if we can take a look at your qvw document? Difficult to know what might be causing this issue?

samsunqv
New Contributor II

Re: Issue with ignoring selections for ranking

Sunny , i was trying to find a way out..Got the solution with the below expression.

aggr(rank(only({

<KPI_PRIORITY=,KPI_NAME=p({<KPI_PRIORITY=, COUNTRY_NAME={'$(vCountry1)'}>}KPI_NAME)>
*
<KPI_PRIORITY=,KPI_NAME=p({<KPI_PRIORITY=, COUNTRY_NAME={'$(vCountry2)'}>}KPI_NAME)>
}-KPI_PRIORITY)), KPI_NAME)

The issue was with the expression to find out the common KPIs between 2 countries..While I had tried putting up the common thing with IF and CONCAT - colleagues pointed out in using the intersect function...and there lies the magic..

Appreciate your response..

Thanks,
Shyam Sundar R

MVP
MVP

Re: Issue with ignoring selections for ranking

If your issue is resolved, then please close this thread.

You can set the complete thread to 'Assumed answered' if you got an answer to your issue by yourself, otherwise please flag Correct  and / or Helpful answers.

Community Browser