topic Re: Issue with ignoring selections for ranking in QlikView

Issue with ignoring selections for ranking

Anonymous — Fri, 03 Feb 2017 06:49:29 GMT

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.

Re: Issue with ignoring selections for ranking

sunny_talwar — Fri, 03 Feb 2017 10:22:40 GMT

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

Re: Issue with ignoring selections for ranking

Anonymous — Sat, 04 Feb 2017 03:39:11 GMT

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

Re: Issue with ignoring selections for ranking

swuehl — Sat, 04 Feb 2017 10:06:21 GMT

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.