3 Replies Latest reply: Feb 4, 2017 5:06 AM by Stefan Wühl RSS

    Issue with ignoring selections for ranking

    ShyamSundar Raghavendran

      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.