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
sunny_talwar

Try this out:

Table:

LOAD Year,

    week,

    Dual(day, Match(day, 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun')) as day

FROM

[..\..\..\Downloads\Test Data.xls]

(biff, embedded labels, table is Sheet1$);

FinalTable:

LOAD *,

  AutoNumber(RecNo(), Year) as Rank

Resident Table

Order By Year, week, day;

DROP Table Table;

Not applicable
Author

can we do something in the set formula?

Not applicable
Author

I am loading different tables with different years hence in my script if I create rank, It wont work in the chart. Can we rank the chart rows according to their year. I used RowNo(total) and its giving me correct sequence number but it restart at new year,it continues till the last row.

anagharao
Creator II
Creator II

Hi,

I've changed the attached as per the output rank required.

Not applicable
Author

It is working but in my case the logic is bit complicated related to the year hence I can not change my script. I need something in set analysis