10 Replies Latest reply: Apr 18, 2011 4:14 AM by PaulThompson RSS

    Problem in sorting with expression!

      Hi

      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!

          Hi

          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!
            s j

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

              • Problem in sorting with expression!

                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!

                    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!

                        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!
                        s j

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

                        that works fine.

                          • Problem in sorting with expression!

                            Thanks Sunil

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

                              • Problem in sorting with expression!

                                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.