Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ndeeleysww
Contributor

Date sorting on bar chart

Hi there,

I've created a bar chart which counts orders and is dimensioned by Month & Year.

If I set the dimension to just year, or just month, the sorting on the chart is fine:

barchart_month.png

However, if I add in the year, then it all goes potty:

barchart_monthyear.png

I'm creating this date in the load script from a column in oracle:

MakeDate(left("CREATION_DATE",4),mid("CREATION_DATE",5,2), right("CREATION_DATE",2)) as FormattedCreationDate,

..and using this as the dimension:

Month([FormattedCreationDate]) & '/' & Year([FormattedCreationDate])

Any ideas what I need to do in order to sort the dates correctly?

Thanks

Neil

1 Solution

Accepted Solutions

Re: Date sorting on bar chart

Hi Neil, you can try to sort by expression:

=Min({1}FormattedCreationDate)

5 Replies

Re: Date sorting on bar chart

Hi Neil, you can try to sort by expression:

=Min({1}FormattedCreationDate)

ecolomer
Honored Contributor II

Re: Date sorting on bar chart

Correct order is:

Year([FormattedCreationDate]) & '/' & Month([FormattedCreationDate])

ndeeleysww
Contributor

Re: Date sorting on bar chart

Brilliant! That worked perfectly. Thanks Ruben.

Employee
Employee

Re: Date sorting on bar chart

You can avoid all of these problems if you treat the dates differently in the script.

  • Don't use string functions to create the FormattedCreationDate. Instead, you should use
    Date#( CREATION_DATE , 'YYYYMMDD' ) as FormattedCreationDate
  • Don't use string functions to create the Dimension. Instead you should use
    Date(MonthStart( FormattedCreationDate ), 'MMM/YYYY' ) as YearMonth

HIC

ndeeleysww
Contributor

Re: Date sorting on bar chart

Thanks Henric - that is a much easier method to use. I'll implement that in future.