topic Re: Sort by Expression does not work as expected in QlikView

Sort by Expression does not work as expected

dafnis14 — Sun, 01 May 2016 07:21:53 GMT

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

Re: Sort by Expression does not work as expected

ashfaq_haseeb — Sun, 01 May 2016 08:03:26 GMT

Hi,

Have a look at attached document.

Regards

ASHFAQ

Re: Sort by Expression does not work as expected

dafnis14 — Sun, 01 May 2016 08:49:06 GMT

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

Re: Sort by Expression does not work as expected

sunny_talwar — Sun, 01 May 2016 12:42:36 GMT

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

Re: Sort by Expression does not work as expected

dafnis14 — Sun, 01 May 2016 17:04:29 GMT

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!

Re: Sort by Expression does not work as expected

sunny_talwar — Sun, 01 May 2016 17:20:09 GMT

Can you check now?

Re: Sort by Expression does not work as expected

Peter_Cammaert — Sun, 01 May 2016 17:30:16 GMT

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

Re: Sort by Expression does not work as expected

dafnis14 — Sun, 01 May 2016 17:31:12 GMT

Hi Sunny,

You can see the expected sort in the straight table:

Thanks!

Re: Sort by Expression does not work as expected

dafnis14 — Sun, 01 May 2016 17:39:46 GMT

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!

Re: Sort by Expression does not work as expected

sunny_talwar — Sun, 01 May 2016 17:43:25 GMT

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

Re: Sort by Expression does not work as expected

Peter_Cammaert — Sun, 01 May 2016 18:33:58 GMT

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

Re: Sort by Expression does not work as expected

dafnis14 — Mon, 02 May 2016 06:11:45 GMT

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

Re: Sort by Expression does not work as expected

dafnis14 — Mon, 02 May 2016 09:09:38 GMT

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

Re: Sort by Expression does not work as expected

dafnis14 — Tue, 12 Jul 2016 06:39:30 GMT

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!