Sort by Expression does not work as expected

[dafnis14]

Dear all,

I would appreciate your assistance in figuring the sort issue I have in the attached demo.

The request is to show a chart based on day/month display of a sale date.

Please note, that sales dates cross 2 years. In such a case, December should be before January.

I added a key to enforce the desired sort. In the  straight table on the right, the expected display is achieved.

In the line chart, I defined a sort expression with the key, but the sort is not as expected:

January is displayed before December.

Attached is an example.

Does anyone have a tip?

Thanks

[ashfaq_haseeb]

Hi,

Have a look at attached document.

Regards

ASHFAQ

[dafnis14]

Hi Ashfaq,

Thanks for your prompt reply!

it works nice when only one year is selected.

But selecting 2 years, for example, disrupts the sort.

Do you have any remedy for that too?..

Thanks...

[sunny_talwar]

What is the expectation for the sort when two years are selected? How would you like it to sort?

[dafnis14]

Hi Sunny,

I would like to have the sort in a chronological order. But as you can see, it's not.

I guess the issue is caused by a service year having sales in 2 different years. For example:

2015 have sales starting in November 2014, and going in 2015.

Thanks for willing to help!

[sunny_talwar]

Can you check now?

[Peter_Cammaert]

Not sure what the exact reason is for this behavior. But you can solve it in a simple way by - in your load script - creating a mapping table that stores the date of the first calendar day of every Service year, and - while loading the data - calculate the Service year day number by subtracting from the row date the starting date of the Service year of that particular row. Use that Service year day number as sorting field.

Best,

Peter

[dafnis14]

Hi Sunny,

You can see the expected sort in the straight table:

Thanks!

[dafnis14]

Hi Peter,

I agree with your input.

I also thought of treating every service month like a specific event", which might fall on different period in different years. That's because this is what the user would select (a month). So when selecting service month,  he would see all sales dates in the format dd/mm.

The issue here is that we have 2 types of periods: Service & sales which are not matching.

Thanks!

[sunny_talwar]

I am still having difficulty understanding what is the sort order needed. Can you may be share an example of one selection with the expected sort and one example of two selection in year field with the expected output