topic Re: How to sort date in a chart? in Qlik Learning Discussions

How to sort date in a chart?

Anonymous — Wed, 05 Nov 2014 06:54:30 GMT

I have a line chart in my dashboard, it contains date like Jan-11,Feb-10.....

I am not able to sort the dates. I want the dates to be look like Dec - 12, Jan - 13, Feb - 13,.............

Thanks,

Re: How to sort date in a chart?

its_anandrjs — Wed, 05 Nov 2014 07:08:11 GMT

Hi,

In sort option write

MonthName(Datefield)

Or if you have Monthname field use that remove any sort option from sort option

Regards

Anand

Re: How to sort date in a chart?

MK_QSL — Wed, 05 Nov 2014 07:19:45 GMT

Go to Sort Tab

Write Below in your Expression

Date#(date,'MMM-YY')

Re: How to sort date in a chart?

Anonymous — Wed, 05 Nov 2014 07:33:21 GMT

Thanks Anand & Manish.

Both ways did not work, it is sorting but not in order.

Note, my YeatMonth data in Excel ( Dec - 12 ), ( Jan - 13 ), ............. are in text format !

Re: How to sort date in a chart?

MK_QSL — Wed, 05 Nov 2014 07:34:09 GMT

Try

Date#(YearMonth,'MMM-YY')

Re: How to sort date in a chart?

its_anandrjs — Wed, 05 Nov 2014 07:39:30 GMT

Then try this way

=Date#(date,'MMM - YY') //in sort order or remove all other sort option if selected

But if possible make another in the load script as

Load

MonthName(Date) as MonthYear

From Location;

Regards

Anand

Re: How to sort date in a chart?

Anonymous — Wed, 05 Nov 2014 08:01:48 GMT

Thanks Anand, but your explanation is not so clear for me, could you explain further!

I have applied your steps, did not work!

Re: How to sort date in a chart?

its_anandrjs — Wed, 05 Nov 2014 08:23:16 GMT

Hi,

I suggested for particular field not for all rows and for that field you have in the data model

MonthName(date) as MonthYear

Date#(date,'MMM - YY') as MonthYear

To convert MonthYear to proper date field is important if you use it as sorting you can do this in front end also but there you may get performance issue.

Regards

Anand

Re: How to sort date in a chart?

Anonymous — Wed, 05 Nov 2014 09:18:51 GMT

Did not work.

Your steps are Not clear .

Kindly explain in more detailed steps.Thanks

Re: How to sort date in a chart?

Nagaraju_KCS — Wed, 05 Nov 2014 09:23:48 GMT

HI Faisal,

In sort tab you can promote to first your YearMonth field..

then apply in your Expression

Try...

Date#(date,'MMM-YY')

Date#(MonthYear,'MMM-YY')

Re: Re: How to sort date in a chart?

Agis-Kalogiannis — Wed, 05 Nov 2014 09:33:31 GMT

Hi Faisal

I am sending you a sample qvw file which solves your problem.

What I did, I first created a sample table with the same format as your month-year fields.

Then I resident loaded these values and converted the values to dates.

After that, I created a table box with the monthyear field and as you can see they are sorted by date, exactly as you want.

The most important thing here is the following:

date#(MonthYear, 'MMM - YY') AS MonthYear

(The 'MMM - YY' mus have exactly the same format as your original text monthyear field)

I hope this helps

Agis

Re: How to sort date in a chart?

Anonymous — Wed, 05 Nov 2014 09:48:20 GMT

Yes I have follwoed these steps, it changed the sorting but NOT in correct order.

Re: How to sort date in a chart?

Nagaraju_KCS — Wed, 05 Nov 2014 09:52:57 GMT

Once you try with simple LoadOrder Option....in sot tab

Re: Re: How to sort date in a chart?

its_anandrjs — Wed, 05 Nov 2014 09:54:51 GMT

Hi,

I suggested for particular field in the load script suppose there is a date field with data as you say i believe it is not in the date format it is text

Dec- 12

Jan - 13

Feb - 13

Mar - 13

Then you have to create that field into date format for (MMM - YY) also maintain the current format

Load

Date#(date,'MMM - YY') as MonthYear

From Location;

And you get month year as

Regards

Anand

Re: How to sort date in a chart?

Anonymous — Wed, 05 Nov 2014 14:46:08 GMT

There are other ways of doing this but I would need a bit more information about how these dates are loaded in your script.

Re: How to sort date in a chart?

Agis-Kalogiannis — Wed, 05 Nov 2014 16:04:44 GMT

Could you check the answers and close the discussion by selecting the Correct/Helpful answers Faisal?

This will help other people use this as a reference.

Thanks

Agis

Re: How to sort date in a chart?

MK_QSL — Thu, 06 Nov 2014 11:14:37 GMT

I gave the same answer long time ago !