Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

vishuraina
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.

WeekYearSalesRank
120172004
120161005
220174001
220163002
320165001
320172503
420173302
420161504
520162203
520171505

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
MVP

Re: Rank a Table

May be this

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

8 Replies
MVP
MVP

Re: Rank a Table

May be this

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

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

vishuraina
New Contributor II

Re: Rank a Table

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

MVP
MVP

Re: Rank a Table

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

vishuraina
New Contributor II

Re: Rank a Table

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

WeekYearSalesRankAvg Rank
1201720044.5 (5)
1201610054.5 (5)
2201740011.5(2)
2201630021.5(2)
3201650012
3201725032
4201733023
4201615043
5201622034
5201715054
MVP
MVP

Re: Rank a Table

Try this

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

vishuraina
New Contributor II

Re: Rank a Table

Thanks for your response, It works.

Community Browser