Year Months NOT sorting in chronological order

[tommaso26]

Hello everyone,

I have an issue with a line chart where I want to display Number of Customers vs Months for three different years in a continuous way.

I want to obtain something like this

However, I cannot sort the months correctly, even adjsuting the sorting options in the chart settings. I obtain this chart: 

As you can see, months are not sorted in any particular way, they seem to be random.

In order to display the month name + the year (as in the chart) I defined the field as follows:

=Num#(Date(FUTURE_DATE,'MMM YY'))

where FUTURE_DATE is

Date(AddMonths(Max(ORDER_DATE),3),'DD/MM/YYYY') AS FUTURE_DATE

I have used this syntax many times and it never gave me wrong results, and I found out that the problem is probably related to the "AddMonths" function. In fact when I remove it, I don't get any error. However I cannot explain why.

Does someone know how to solve this?

Thank you very much

Tommaso

[Gysbert_Wassenaar]

You're using a string -to-number conversion function num# that tries to make numbers from strings. Any string that can't be turned into a number, which in your case is every value, stays a string. And gets sorted as strings.

Try leaving out the num# function. Dates formatted as MMM YY should still be sorted as dates. If not you can always use the Sort by Expression option in the Sort section of the chart properties and sort by num(FUTURE_DATE)

[tommaso26]

Hi @Gysbert_Wassenaar,

thank you for the answer.

If I remove the num# function I don't get the "Month Name + Year" (ex. Jan 2016), but I just get the date in the default format (1/1/2016). See below

Field is defined as Date(FUTURE_DATE,'MMM YY')

I don't get why it doesn't show "Month Name + Year"

[Or]

You should be able to just use MonthName(WhateverDateYouWant) to get the month name.

[tommaso26]

Hi @Or ,

thank you for your asnwer.

Using the MonthName function I get closer to the expected output but the chart still doesn't display "Month Name + Year". See below

Field is defined as: MonthName(FUTURE_DATE)

Results are now correctly grouped and sorted, however the x-axis still doesn't display the right output. I need to show "month name + year", for example 'Jan 16'.

Any ideas?

Thank you very much

[Gysbert_Wassenaar]

Try playing around with the Continuous setting of the x-axis.

[Or]

As mentioned by @Gysbert_Wassenaar  above, you probably have continuous axis enabled, which causes the engine to pick its own formats. If you'd like to have each month treated as a discrete dimension value, switch that off. Keep in mind that this will also prevent the engine from filling in missing months, though.

[tommaso26 · Accepted solution]

Thank you very much @Gysbert_Wassenaar and @Or ,

I managed to find the solution by using both your suggestions.

So I did the following:

- Field is defined as: MonthName(FUTURE_DATE)

- Sorting is set as: Numerically, Ascending

- X-axis is set with Continuous scale OFF

The desired output is obtained as shown below

I still have some doubts why it wasn't working using my way, but I will figure it out.

Thank you very much for the support!

Tommaso