Rank datapoints relative to others in the same sub-group

[#1]

Hello all

I'd like to know whether the following is possible in Qlikview.

I have a list of datapoints (call them orders).  The orders are categorised according to the type of product and name of customer.

So far I've pulled my data into a straight table.

I'd like to add a field showing the rank of each order (in terms of value) relative to all other orders for the same product from the same customer.  I've managed to do this in Excel using a small test dataset but would ideally like to this within Qlikview using my real dataset which is much larger.

Can anybody write an expression in Qlikview that gives the same result as that in the rank column below, using the raw data from the first 3 columns?

Name	Product	Value	Rank within name and product combo group
John	Apples	32.75	1
John	Apples	14.88	2
John	Oranges	71.01	1
John	Oranges	27.13	2
John	Plums	53.40	1
John	Plums	25.91	2
John	Plums	12.79	3
John	Plums	2.32	4
Mike	Apples	73.76	1
Mike	Oranges	68.34	2
Mike	Oranges	92.72	1
Mike	Oranges	18.36	3
Mike	Plums	17.60	4
Mike	Plums	96.75	1
Mike	Plums	80.95	2
Mike	Plums	48.57	3

Thanks

John

[#2]

I think it is doable. See attached (or the code below for if you have Personal edition).

testdata:

load * inline [

Name,          Product,          Value,          Rank

John,          Apples,                  32.75,          1

John,          Apples,                  14.88,          2

John,          Oranges,              71.01,          1

John,          Oranges,        27.13,          2

John,          Plums,                  53.40,          1

John,          Plums,                  25.91,          2

John,          Plums,                  12.79,          3

John,          Plums,                    2.32,          4

Mike,          Apples,                  73.76,          1

Mike,          Oranges,                68.34,          2

Mike,          Oranges,        92.72,          1

Mike,          Oranges,        18.36,          3

Mike,          Plums,                  17.60,          4

Mike,          Plums,                  96.75,          1

Mike,          Plums,                  80.95,          2

Mike,          Plums,                  48.57,          3

]

    ;

NoConcatenate

load * ,

if(

//reset to 1)

//first row

(isnull(peek(myRank))

or

previous(Name)<> Name

or

Previous(Product)<>Product)

,1,peek(myRank)+1) as myRank

Resident testdata

order by Product asc ,Name asc,Value desc

;

drop table testdata;

The column myRank is the same as your column...    

[whiteline]

Hi.

Just load you data.

Create a Chart with Name, Product, Value as dimensions.

The expresison is simply =rank(Value)

[#4]

Thanks both

Whiteline, your answer is giving me what I need, but there's a further complication.  There are other fields in my data which I also want to show in my table, but without these narrowing the groups within which my ranking applies.

Can this be done?

Thanks

John

[#5]

I think this is done by in some way using aggr and nodistinct within the rank expression but can't quite get it to work.

[#6]

Roberto

Thanks also for your approach.  I'm having trouble replicating your code in a way that allows the data to be loaded from excel (rather than being entered via the script).  Could you provide an example?

Thanks

John

[whiteline]

Try:

=aggr(rank(Value), Name, Product, Value)

Depending on your data model In case you want to repeat the value use also nodistinct statement:

=aggr(nodistinct rank(Value), Name, Product, Value)

[#8]

I think I need an example excel to map my solution on yours. Inline or excel should be no difference by the way.

Can you provide a (small?) example xls?

Additionally: I'm busy today, so it will be tomorrow somewhere (earliest)

[whiteline]

Hi.

I just took part of your data and added Third dimension for demonstration (nodistinct):

LOAD * INLINE [

Third, Name,     Product,     Value

1, John,     Apples,             32.75

2, John,     Apples,             14.88

2, John,     Oranges,        71.01

2, John,     Oranges,        27.13

3, John,     Oranges,        27.13

];

Then create a cahart - straight table:

Third, Name,     Product,     Value - dimensions

and expressions to test:

=rank(Value)

=aggr(rank(Value), Name, Product, Value)

=aggr(nodistinct rank(Value), Name, Product, Value)