Rank a Table

[Anonymous]

Hi All,

I have the data in the below format & want to rank as per the sales grouped by year. The rank should come as below in Red.

Week	Year	Sales	Rank
1	2017	200	4
1	2016	100	5
2	2017	400	1
2	2016	300	2
3	2016	500	1
3	2017	250	3
4	2017	330	2
4	2016	150	4
5	2016	220	3
5	2017	150	5

I'm able to rank across the tables irrespective of the year, but i need  the result year wise as above. I thought using the aggr function should have solved this, but somehow unable to get the desired result. Any help on this is much appreciated.

[sunny_talwar · Accepted solution]

May be this

Aggr(Rank(Sum(Sales)), Year, Week)

[Anonymous]

Hey Sunny,

Thanks for the help, i was doing the same but somehow the calculated measure i was using had issues, over complicated things. Thanks it works now.

Also i want to get one more column - avg rank across years.

ex - if week 2 had rank 6 & 8 in year 2016 & 2017. then the avg rank column should have  7 (6+8/2) for week 2 across 2016 & 2017. Would be great if you could help with this.

[sunny_talwar]

With Year and Week as dimension? Try this

Avg(TOTAL <Year> Aggr(Rank(Sum(Sales)), Year, Week))

With only week as dimension

Avg(Aggr(Rank(Sum(Sales)), Year, Week))

[Anonymous]

Not working. It is throwing the same result across all rows.

[sunny_talwar]

What else do you want? I am not sure I understand the requirement?

[Anonymous]

I need the avg Rank against  same week  numbers. Below is the example.

Week	Year	Sales	Rank	Avg Rank
1	2017	200	4	4.5 (5)
1	2016	100	5	4.5 (5)
2	2017	400	1	1.5(2)
2	2016	300	2	1.5(2)
3	2016	500	1	2
3	2017	250	3	2
4	2017	330	2	3
4	2016	150	4	3
5	2016	220	3	4
5	2017	150	5	4

[sunny_talwar]

Try this

Avg(TOTAL <Week> Aggr(Rank(Sum(Sales)), Year, Week))

[Anonymous]

Thanks for your response, It works.