7 Replies Latest reply: Jan 8, 2017 4:10 AM by Petter Skjolden RSS

    Need help with ranking

    Alexander Tsibizov

      Dears,

      Could you please help me with rank functions.

      I have the following:

         

      BizDealValue
      ARed10
      BBlue15
      CSmall5
      AFlow8
      CSmall6

       

      So, I'd like to rank in separate column by Biz, By Deal, By Value.

       

      Thank you in advance.

        • Re: Need help with ranking
          Petter Skjolden

          I am not sure if this is what you had in mind?

           

          2017-01-07 01_39_02-_Qlik Community - 2017-01-06 - Ignore the current selection within aggr() functi.png

           

          I have associated both Biz and Deal with a numeric value so they can be ranked. Maybe you just want plain sorting and creating dual-values with the Dual()-function will enable various qualitative text strings to be sorted according to a ranking value.

           

          For instance:

           

          QUALITY

          Bad          0

          Neutral     1

          Good        2

          Better       3

          Best         4

           

          Using a dual-function Dual( <text> , <value) will make this association and whenever the field values are sorted it will prefer to use the numeric part to determine sort order. If you don't have a dual-value but pure text it will be sorted by "natural sort order" (almost like alphabetical sort except for multi digit numbers).

            • Re: Need help with ranking
              Alexander Tsibizov

              Petter, thank you for you answer.

              I am not sure that this is exactly what I look for.

              I found the key if I want to rank each possitions (as in the example below).

              I created new dimension w/ the formula: aggr (rank([Actual]),[Actual]), but could not find the way how to rank by biz and deal (oppt in my below example).

              In other words I want to have two more columns: where A has rank 1, B-2, C-3 and the second one where Oppt Calik has rank 1, TPO - 2, Small - 3 and Flow - 4.

              The problem I faced is that in 'Small' I always have '-'.

              Thanks.

              Alexander

              Capture.JPG

                • Re: Need help with ranking
                  Petter Skjolden

                  I don't see why you need to use Agg() at all. That might be the cause of the problem you mention: in 'Small' I always have '-'. The reason is that Aggr() might return 0, 1 or multiple values. And if you get multiple values and you don't have an aggregation function that resolves them into one value to show in one row you will get the '-'.

                   

                  Try to do as I suggested - just use the Rank() function alone without any Aggr(). You have to use TOTAL inside the Rank() if you have more than one dimension which you obviously have here.

                   

                  So have a close look at this screenshot an notice the expressions that appears as column headers here:

                   

                  2017-01-07 11_36_11-Qlik Sense Desktop.png

                   

                  In the load script I have associated the Biz with numeric values 1, -1, -2, -3 as you mentioned.

                  Likewise I have associated Deal with number values also in a similar range.

                   

                  2017-01-07 12_36_02-Qlik Sense Desktop.png

                    • Re: Need help with ranking
                      Alexander Tsibizov

                      Petter,

                       

                      As I understand, your suggestion is manually assosiate Biz with numeric value - this is not an option for me, as in the real example I have hundreds of deals and busensses.

                      W/o assosiation I have the following ranking for Biz (please see below) what is incorrect since the right rank for biz: First A (total actual =18), second B (15) and third C (11). So, I need that in the column 'Rabk Biz' there will be something like this (in the considered example):

                      C 3

                      C 3

                      A 1

                      A 1

                      B 2

                      Capture.JPG

                        • Re: Need help with ranking
                          Petter Skjolden

                          I don't follow you completely - is there buisnesses called A, B, C and so forth? To me this seems like a demo data set - not something real.

                           

                          Gettting a sequential numeric value out of the english alphabet from a-z is very straightforward as they occupy the code values from 65 to 90 for uppercase and lettes:

                           

                               Ord('A') = 65   Ord('Z') = 90

                           

                          So then it is a matter of just using the Ord()-function. When it comes to Oppt/Deal you probably have to have some kind of mapping table between text and ranking values.

                  • Re: Need help with ranking
                    Manish Kachhia

                    Create a Straight table

                     

                    Dimension

                    Biz

                    Deal

                    Value

                     

                    Expression

                    Num(Rank(Total SUM(Value),4))