Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
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
Highlighted
Honored Contributor

Re: Ranking according to dimension

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;

Highlighted
Esteemed Contributor III

Re: Ranking according to dimension

Let me know if this work around works for you.

Highlighted
Not applicable

Re: Ranking according to dimension

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

Highlighted

Re: Ranking according to dimension

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.

Highlighted
Contributor II

Re: Ranking according to dimension

Hi,

Is this what you were after?

Highlighted
Not applicable

Re: Ranking according to dimension

Hi,

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

Highlighted
Not applicable

Re: Ranking according to dimension

Ranking should restart at new year.

Highlighted
Not applicable

Re: Ranking according to dimension

I have added required output rank in green highlighted column

Highlighted
Not applicable

Re: Ranking according to dimension

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