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

[Peter_Cammaert · Accepted solution]

In a QlikView document, you can either display the two calendars but switch the selection instruments from one calendar to another (by way of a button or something) or display the selection boxes for both calendars at the same time. The latter not being very intuitive IMHO. The charts and tables should react to both the selections and their (possibly changed) environment in the best predictable way.

The issues you face are comparable to those present in financial QlikView documents which display both a financial year that is offset in a particular way from a calendar year. There are many examples and discussions about this in the community. A search may provide inspiration.

Best,

Peter

[dafnis14]

Hi Peter,

Thanks for the additional tips. In the document, I implement 2 calendars (2X2) for each period.

But for a report, I need to add the the sort field in order to enable comparison of a service month in different years.

As opposed to financial periods which have an explicit begin & end date, a service month does not have a predefined

dates range. That's what causes the current issue.

To this end, I haven't find a working example in the community.

Thanks..

Dafnis

[dafnis14]

Hi Sunny,

Sorry, I haven't noticed your reply.

In the above screenshot, the lines should start from November and not January.

But following what Peter wrote, with the current data it can't be achieved. That's because for every service month,

the sales period should be defined according to a minimum date, and then allocate a day number to sort by.

Thanks...

[dafnis14]

Hi Peter,

It took a while, but I have used your tip and it resolved the issue: to display dates from different years

based on an expression sort.

"...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..."

Thanks!