# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor II

## Rank a Table

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.

1 Solution

Accepted Solutions
MVP

## Re: Rank a Table

May be this

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

8 Replies
MVP

## Re: Rank a Table

May be this

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

New Contributor II

## Re: Rank a Table

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.

MVP

## Re: Rank a Table

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))

New Contributor II

## Re: Rank a Table

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

MVP

## Re: Rank a Table

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

New Contributor II

## Re: Rank a Table

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
MVP

## Re: Rank a Table

Try this

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

New Contributor II

## Re: Rank a Table

Thanks for your response, It works.