Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
May be this
Aggr(Rank(Sum(Sales)), Year, Week)
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.
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))
Not working. It is throwing the same result across all rows.
What else do you want? I am not sure I understand the requirement?
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 |
Try this
Avg(TOTAL <Week> Aggr(Rank(Sum(Sales)), Year, Week))
Thanks for your response, It works.