Sorting Date in Numeric Format

[niha]

Hi,

I wanted to add few more. i have two date format. 

1. I need the Year month to come like 201701, 201702, 201703......201601 so on...

2.  I also need to sort like 201601, 201602, 201603 s.........201701, 201702 in both cases. Please advice

Niha

[da_pagnott]

Hello,

could be something like this.

First, I suggest to set your date format like 201701, 201702 and not 20171, 20172, ...

After that, you can order using a formula like this:

= left( DATE, 4 )*1000 + right ( DATE, 2)

It worked fine for me!

[YoussefBelloum]

Hi,

take a look at your main variables (the ones you can find at the beginning of your script) and see if the format is ok for you, change it if not. or your can use the Date() function to change it directly on your dimension.

Also, to sort your dates, you can use Max(Date) on the sorting area, descending or descending.

give all this a try and let me know

[niha]

Hi,

Thanks. I used this code and it gave data format like this : 201601. 201602....so on and the sorting as AUTO .

Looks okay to me now. 

Date(MonthStart(Date#([Date of First Service],'YYYY/MM/DD')), 'YYYYMM') as DFSYearmonth,

Thanks

[YoussefBelloum · Accepted solution]

Cool, you can close the thread then

good luck