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

    Need help with ranking

    Alexander Tsibizov


      Could you please help me with rank functions.

      I have the following:




      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:



          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 '-'.




                • 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



                      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


                        • 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








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