Aggregation over sub dimensions

[#1]

1. Greetings.

I have a feeling the answer involves the Aggr() function; however, as yet I can not get it to work.

I have a table similar to the example below

Order #	SKU	Lead time (days)	Rank
169339	1112	70	3
169339	2231	111	6
169339	3451	141	8
169339	3566	166	9
169339	8582	89	5
178040	1112	58	1
178040	2231	71	4
171193	1112	60	2
178111	1112	121	7

I can perform ranking and averaging etc on the table when I have one SKU selected, but when the selection is removed the ranking and average refers to all data displayed in that column (as shown above).

What I want to achieve is have an unfiltered table but show the ranking etc per SKU.

For example:

Order #	SKU	Lead time (days)	Rank
169339	1112	70	3
169339	2231	111	2
169339	3451	141	1
169339	3566	166	1
169339	8582	89	1
178040	1112	58	1
178040	2231	71	1
171193	1112	60	2
178111	1112	121	4

Please can anyone help?

[#2]

Hi Anbu

Thank you for your help. I am looking in the QlikView help file but can't yet figure out how your solution works.  As I am trying to learn from this could you briefly explain how this expression works?

Am I right in thinking that this:

aggr(rank(-[Lead time (days)]),SKU,[Lead time (days)])

returns the ranking, while the rest of the alt() and above() have been used to fix the sort order??

Thanks and kind regards

Oli

[anbu1984]

aggr(rank(-[Lead time (days)]),SKU,[Lead time (days)]) 

- Aggr on SKU,[Lead time (days)] creates a table like below in memory. Aggr is used to ignore your other dimension Order # while ranking

SKU	Lead time (days)
1112	70
2231	111
3451	141
3566	166
8582	89
1112	58
2231	71
1112	60
1112	121

- Then Rank is calcuated on [Lead time (days)] based on above data. 

Lead time, Rank

58, 1

60, 2

70, 3

121, 4

Rank of negative Lead time

Lead time, Rank

-58, 4

-60, 3

-70, 2

-121, 1

Alt( expr, Above(expr)) - Alt returns value of expr which provides non-null value. In our case, Rank generates null value, if you have same Lead times. So Alt is used to return to same rank from the above row

[anbu1984]

Generating rank in script

Initial:

LOAD [Order #],

     SKU,

     [Lead time (days)],

     [Desired result]

FROM

(biff, embedded labels, table is Sheet1$);

NoConcatenate

Final:

Load *,If(SKU = Previous(SKU) And [Lead time (days)] = Previous([Lead time (days)]),Peek(Rnk1),Rnk) As Rnk1;

Load *,AutoNumber(RecNo(),SKU) As Rnk Resident Initial Order by SKU,[Lead time (days)];

Drop table Initial;