Sort by expression not working as expected

[#1]

Hello,

I raised this on Friday on back of an older thread relating to same issue but haven't had a response. Just trying as a new post to see if anyone sees it that missed the original.

Has anyone else come across this and worked out what is happening?

I have a pivot table with sort on expression. This table shows last 13 weeks sales so in first 13 weeks of year lowest numbers show last across top of table..

If(vCurrentWeek < '14', wildmatch(Week,'40','41','42','43','44','45','46','47','48','49','50','51','52','1','2','3','4','5','6','7','8','9','10','11','12','13') ,

wildmatch(Week,'1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52'))

This works great but if a week is selected this week appears last in sort even though state is unticked on sort screen.

I have expression, numeric and text selected on sort. Get same results with just expression.

Without the expression the sort works as I would expect even with a selected week.

Week is the second of two dimensions. The first is just set to sort by text and shows down vertical side.

Any help appreciated.

I've attached a qvd which shows the issue. Apologies for the data - all I could think of. An alternative that would work for me would be to disable the selection on problem field. I do require selection on other values though (Type in example).

Regards, Paul

[#2]

Hi,

try this expressión in the sort tab: 'One,Two,Three,Four,Five,Six'

Hope this helps

Regards!

[#3]

Thanks for taking a look Gabriela,

That does work for the example provided but when I tried in my application we still got the same results.

I've changed the data to reflect more accurately what application is doing.

I've set the sort to 1,2,3,6,5,4 now and changed values in "number" to 1,2,3,4,5,6.

I also have an IF statement in the applicationwith the issue (see previous post) as only want to do non-standard sort for first 13 weeks of year but the example seems to show the issue occurs without this.

Regards, Paul.

[chris_johnson]

Hi Paul,

With regards to your original example I have attached a way of sorting the Number field in an 'unnatural' way. So if there doesn't seem to be a way of sorting the field either alphabetically or by number (for example in the case of sorting by status) you can set the sort manually in the script. I set the sort order in an inline table (which was dropped at the end).

I then set the sort in your pivot table to be on load order so that it ordered the field based on how I set it in the inline table.

It's a handy way of setting the sort order if you can't do it any other way although it has to be maintained of course so if you have add any more status to the data then you will need to set how it is ordered manually in the inline table (otherwise I think QlikView will just stick any other status values on the end).

Have a look and see what you think. With regards to your initial question about week numbers maybe it would be best to have a week-year field to use instead?

Hope this helps in some way,

Chris

[#5]

Thanks Chris,

The change to load order might give us a workaround but I need to have a think about whether it will effect any other parts of the application + not sure I could deal with the different sorts based on week number that I have in the application.

Week-Year would be a solution normally and I have the field available but this table also compares the week from one year to the week from another and I need to ignore the year to acheive this. I'll attach an example with data omitted.

Once again many thanks for having a look.

Regards, Paul.