Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need help with ranking

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.

7 Replies
petter
Partner - Champion III
Partner - Champion III

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

Not applicable
Author

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

MK_QSL
MVP
MVP

Create a Straight table

Dimension

Biz

Deal

Value

Expression

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

petter
Partner - Champion III
Partner - Champion III

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

Not applicable
Author

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

Not applicable
Author

Thank you for reply, but this works in the same way as 'Rank (total [values]) and gives ranking for values, but now I am looking for ranking on Biz and Oppt/Deal level.

petter
Partner - Champion III
Partner - Champion III

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.