Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Ranking according to dimension

I have a straight table containing dimensions,

Year , Week Number , Day name

2016 , 1                    , Mon

2016 , 2                    , Tue

2016 , 2                    , Wed

2015 , 1                     ,Mon

2015 , 3                    , Tue

I have attached the sample data. I would like to rank those rows group by Year. Like below

Year , Week Number , Day name , Rank

2016 , 1                    , Mon         ,  1

2016 , 2                    , Tue         ,   2

2016 , 2                    , Wed        ,   3

2015 , 1                     ,Mon         ,   1

2015 , 3                    , Tue          ,   2


Ranking should restart with new year. Please help.


Thanks,

Mugdha

14 Replies
effinty2112
Master
Master

Hi Mugdha,

A script based solution could be:

Data:

LOAD Year,

     SAT_FISCAL_WEEK_NUMBER,

     DAY_NAME,

     rank,

     ytd

FROM

[Test Data.xls]

(biff, embedded labels, table is Sheet1$, filters(

Remove(Row, Pos(Top, 2))

));

RankedData:

LOAD *,

AutoNumber(SAT_FISCAL_WEEK_NUMBER & '|' &DAY_NAME,Year) as RankNo

Resident Data Order by  SAT_FISCAL_WEEK_NUMBER,DAY_NAME;

DROP Table Data;

vishsaggi
Champion III
Champion III

Let me know if this work around works for you.

Not applicable
Author

Sorry but its not working for me,I am getting some random numbers in between sequential numbers

sunny_talwar

If what Andrew or vishsaggi provided doesn't work for you, would you be able to elaborate on what exactly is needed? I feel I am not really sure what is needed here.

anagharao
Creator II
Creator II

Hi,

Is this what you were after?

Not applicable
Author

Hi,

I have attached the sample data and required output rank in green highlighted column.

Not applicable
Author

Ranking should restart at new year.

Not applicable
Author

I have added required output rank in green highlighted column

Not applicable
Author

It would be helpful if some one could suggest me the set formula