topic Re: Sorting date in specific order in App Development

Sorting date in specific order

Michaels — Sat, 16 Nov 2024 01:06:32 GMT

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

Re: Sorting date in specific order

Anil_Babu_Samineni — Mon, 16 Nov 2020 20:19:43 GMT

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

Re: Sorting date in specific order

Michaels — Mon, 16 Nov 2020 20:23:47 GMT

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

Re: Sorting date in specific order

Anil_Babu_Samineni — Mon, 16 Nov 2020 20:29:12 GMT

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

Re: Sorting date in specific order

Michaels — Mon, 16 Nov 2020 21:07:51 GMT

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

Re: Sorting date in specific order

edwin — Mon, 16 Nov 2020 23:33:23 GMT

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 :

Re: Sorting date in specific order

QFabian — Tue, 17 Nov 2020 00:23:57 GMT

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':

Re: Sorting date in specific order

Michaels — Tue, 17 Nov 2020 09:43:12 GMT

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