Date sorting on bar chart

[ndeeleysww]

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:

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

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

[rubenmarin · Accepted solution]

Hi Neil, you can try to sort by expression:

=Min({1}FormattedCreationDate)

[ecolomer]

Correct order is:

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

[ndeeleysww]

Brilliant! That worked perfectly. Thanks Ruben.

[hic]

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]

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