Ranking according to dimension

[#1]

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

[effinty2112]

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]

Let me know if this work around works for you.

[#4]

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]

Hi,

Is this what you were after?

[#7]

Hi,

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

[#8]

Ranking should restart at new year.

[#9]

I have added required output rank in green highlighted column

[#10]

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