Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

rupaliqlik
New Contributor III

Date sorting

Hi Experts,

I have YearMonth Column in my datamodel which is like this ...

2016-Apr

2016-May

2016-Jun

2016-Jul

2016-Aug

2016-Sep

2016-Oct

2016-Nov

2016-Dec

2017-Jan

2017-Feb

2017-Mar

2017-Apr

2017-May

2017-Jun

but when I fetched this column against any dimension in pivot it can not come in same order.It is coming like this

2016-Apr

2016-Aug

2016-Jun

2016-Jul

What would be the  right way to sort this column in same fin order.

Thanks,

Rupali

7 Replies
felipedl
Valued Contributor III

Re: Date sorting

Its sorting for the text, not a numerical representation.

I would advise you to create a date with the function date#([Field],'format') so that it creates the dual for the date your using.

In your case, the sort is correct, since 'Apr' < 'Aug' and so on.

After that, the sorting issue will be addressed since it will be using the numeric representation of the date to sort.

Felipe.

rupaliqlik
New Contributor III

Re: Date sorting

Unable to sort.

aarkay29
Valued Contributor

Re: Date sorting

try sort by expression Monthstart(Date)

ankur_abhishek
New Contributor III

Re: Date sorting

Hi Rupali,

Use the below expression in sort tab of pivot table.

date(date#([YearMonth],'YYYY-MMM'),'MM')

Cc.JPGCaptvvure.JPG

passionate
Valued Contributor

Re: Date sorting

Hi Rupali,

You can even sort by adding below expression to sort by expression:

Pick(Match(YearMonth,'2016-Apr','2016-May','2016-Jun','2016-July'),'1','2','3','4')...............So on for all the months

And Make it by Ascending.

Option 2:

You can even sort it by YearMonth directly in sort by expression

Regards,

Pankaj

atkinsow
Valued Contributor II

Re: Date sorting

Change your field in your data model to be a dual field.  Then you can set it to sort numerically.

dual(YearMonth,left(YearMonth,4) & num(month(date#(right(YearMonth,3),'MMM')))) as YearMonthNew

rupaliqlik
New Contributor III

Re: Date sorting

DEar Expert,

I'm using MonthName(today())  at backend and calculate dimension   

Community Browser