Sorting date in specific order

[Michaels]

Hello,

I have a linechart with Dimension = Date (format mm yyyy) and measure = sum(sales)

Dates are like this:

Dec 2017 

Dec 2018

Nov 2019

Dec 2019 

Nov 2020

For analysis purpose I need to have this order:

Dec 2017 

Dec 2018

Dec 2019

Nov 2019

Nov 2020

Is it possible to do this ?

FYI :

Dates are dynamic based on today month. Basically it's 

Dec N-3

Dec N-2

Dec N-1

Month N-1

Month N

[Anil_Babu_Samineni]

I would do Year dimension as sorting measure with normal file (MMM-YYYY) format

[Michaels]

@Anil_Babu_Samineni but how to sort the months ? I want dec 2019 before nov 2019 

[Anil_Babu_Samineni]

That also can be done using Month and Year format in number then use this field as descending order.

[Michaels]

I tried to sort by date(date#(DATE, 'mm yyyy') decreasing but it give me

Nov 2020

Dec 2019

Nov 2019

Dec 2018

Dec 2017

[edwin]

either create a separate table that has a dual of the month and its order (not a date); or add that field into your calendar:

NoConcatenate
load dual(month,RowNo()) as MONTH, month as MONTHTxt inline [
month
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
];

NoConcatenate
calendar:
load date(Today()+RowNo()*5) as Date, text(Month(date(Today()+RowNo()*5))) as MONTHTxt, year(date(Today()+RowNo()*5)) as YEAR;
load 1 as num AutoGenerate (365);

these two tables are linked by the text of the month, sine the MONTH field is a dual you can sort it by month name.  just add this field and YEAR as dimensions :

[QFabian · Accepted solution]

Hi @Michaels , lot of fun doing this, please try :

//i changed the order of the records to test the functionality

DateX:
load * inline [
Date, Sales
1-11-2020,450
1-12-2017, 250
1-12-2018,150
1-11-2019,300
1-12-2019,340
];

Date:
Load
Date,
monthname(Date) as Period,
month(Date) as Month,
Year(Date) as Year,
Sales
Resident DateX;

drop table DateX;

DateFinal:
Load
recno() as id,
Date,
Period,
Month,
Year
Resident Date
order by
Month desc , Year asc;
drop table Date;

and in the chart, just order by 'id':

[Michaels]

Thank you @QFabian  !! That's exactly what I'm looking for !