Ranking YYYY_W notation correctly (2022_6 is ranked between 2022_52 and 2022_7)

[FVP]

Hi all,

I'm having trouble finding a solution for what seems like a simple fix. I have export file that uses a date notation of YYYY_W. 

For example:
2022_1 (Being year 2022, week 1)

2022_2

2022_3

2022_4

2022_5
2022_6

2022_52 (being year 2022 and week 52)

The issue is that when I use this data, it ranks it as follows:

2022_1 

2022_2

2022_3

2022_4

2022_5

2022_52
2022_6

How can I change the ranking so that it ranks correctly?

Thanks for all the help 🙂

[v-shex-msft · Accepted solution]

HI @FVP,

In fact, they are text values so they will be sorted by text characters instead of the numbers.

Perhaps you can add a new column to your table to convert the year week as numeric values,(year *100 +week number) then you can set the new field as ‘sort by column’ of the text field to help raw table fields work with customize sort orders.

Regards,

Xiaoxin Sheng

[FVP]

Hi @v-shex-msft 

Sorry for my late reply. This solution works, and makes a lot of sense. 
Thanks for your reply and time 🙂