topic Problem in sorting with expression! in QlikView

Problem in sorting with expression!

Fri, 21 Jan 2011 13:40:11 GMT

I am using the following expression in one of chart for sorting the dimension. The problem what I am facing here is that whenever I select 2011 it is not getting sorted properly but for the rest of the years it is getting sorted properly. One of the possible reason for this may be because 2011 contains only one period(A1).

Can some one please check and let me know what could be done with this expression. For your understanding, A1,A2,...,A12 are months only which is named like this.

For your information A1-A12 are months only it is calculated in script.

Expression:

=wildmatch(PERIOD,'A1','A2','A3','A4','A5','A6','A7','A8','A9','A10','A11','A12')

When selecting 2011 sorting in proper may be because it contains only one period(A1):

'A1','A10','A11','A12','A2','A3','A4','A5','A6','A7','A8','A9'

When I select other years(2010,2009) sorting is proper as shown below. May be because it contains all the period(A1-A12)

'A1','A2','A3','A4','A5','A6','A7','A8','A9','A10','A11','A12'

Problem in sorting with expression!

Tue, 25 Jan 2011 07:00:12 GMT

Does any knows the solution for this. I am still working on this problem but not able to find out the solution for this.

The reason why it is not working is may because 2011 contains only one period A1 and for other periods there is no value(A2-A12). So ths expression works only for those year where the periods are there. When there is no value of the periods for particular year(2011) qlikview not considering this expression to sort. It is displaying the periods by default.

I think we may need to find out where those value are taken in qlikview. I think it is just displaying the periods as it is.

Just a thought!

Problem in sorting with expression!

suniljain — Tue, 25 Jan 2011 08:15:08 GMT

Rght(Period,2) it will gives you period in number so you can sort it easily

Problem in sorting with expression!

Tue, 25 Jan 2011 08:32:37 GMT

Thanks again Sunil

Thats true but I don't want to display the period in number. I want to display the period in the same format only (A1,A2,...A12)

Problem in sorting with expression!

Tue, 25 Jan 2011 12:11:48 GMT

Hi Attitude,

Please give Desc in Script So it will sort by Desc Order.

or else you can also Sort it in QlikView by Sorting it in Sort Tab Descending.

Regards,

Pranav

Problem in sorting with expression!

Tue, 25 Jan 2011 13:39:22 GMT

Hi Pranav,

I am not sure what how to give Desc in the script could you please explain it in a better way.

By the way I have already tried sorting in Descending order from the sort tab. It does work for 2009 and 2010 but not for 2011 which contains only 2 periods(A1 and A2).

Problem in sorting with expression!

suniljain — Tue, 25 Jan 2011 13:39:44 GMT

you can sor by my previous suggestion but dispaly in original manner.

that works fine.

Problem in sorting with expression!

Tue, 25 Jan 2011 13:52:15 GMT

Thanks Sunil

I have tried it but it didn't work in that way.

Problem in sorting with expression!

Fri, 15 Apr 2011 15:34:18 GMT

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.

Regards, Paul.

Problem in sorting with expression!

Fri, 15 Apr 2011 16:22:48 GMT

I've tried to replicate the issue in this example.

Apologies for data being a bit random. It's Friday p.m?

Sort seems even less predictable in this example than in the application.

A workaround would be to not allow selection of "Number" as selection does not effect data displayed but need to allow selection on other dimension and can't find a way to only disable one dimension from being selectable but I may have missed this.

Hope the attachement works, I've not uploaded one before.

Regards,

Paul.

Problem in sorting with expression!

Mon, 18 Apr 2011 08:14:55 GMT

Just giving this a bump up. Probably should have created a new thread rather than tagging onto this one. Any ideas?

Regards, Paul.