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?

[anbu1984]

Aggr(Rank([Lead time]),SKU)

[#3]

Hii,

Please use this expression for solution:-

=aggr(rank([Lead time (days)]),SKU,[Order #])

And also find the attached QVW .

[#4]

Hi

Firstly - Thanks to everybody for their advice

I have attached an example and for unknown reasons your suggestion (which works in your example) acts differently in mine???

They are basically the same but I am unable to spot what's wrong...

[#5]

Hi

Thank you for your advice.  I have now attached an example in reply to Prince Anand.  This didn't quite solve my problem but I think it is a step in the right direction.

[#6]

The Only difference is that It is giving result in Descending Order but

I think you want the result in Ascending Order.

Thanks & Regards

Prince Anand

[#7]

Please check the expression for taht solution:-

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

I think you want the result like this:-

[#8]

The difference I am talking about is that the table on the left has more results visible.  For example, using this expression I am losing all results for SKU 3487 and only getting partial results for 1123...

I have been trying to play with the aggr() function but not arrived at the desired result.

Thanks again

Oli

[#9]

I tried this

=aggr(rank(total [Lead time (days)],0,1),[Lead time (days)], SKU,[Order #])

which shows all the results but I am back to the rank being for the whole table not per SKU/Lead time.

Also - when I removed the total from the rank formula I get all lines as rank 1.

[anbu1984 · Accepted solution]

Check this qvw

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