13 Replies Latest reply: Feb 23, 2012 10:50 AM by James Rozee RSS

    Listbox selection order

      Hi,

       

       

      I currently have a macro that displays the selected values of a field in a listbox.

       

      set s = ActiveDocument.Fields("DrillField").GetSelectedValues

       

      for i = 0 to s.Count-1
      msgbox(s.Item(i).Text)
      next

       

       

      However, no matter the order I select the values in the list box, the array of selected values always comes back the same as the listbox's sort order.  Is there a way to retrieve the selected values of the field in the order that they were selected?

       

      Thanks.

       

      James

        • Re: Listbox selection order

          What I'm trying to do is the following:  I want to be able to dynamically drill a pivot by adding and removing dimensions.  But I need a way for the user to choose the dimension to breakdown the pivot by and the order of the dimensions.  To that end, I have a data island that lists the dimensions to drill into, the field name of the dimension and the drill order.

           

          I have the macro able to trigger when an option in the listbox is checked (using Windows checkmark box style).  The macro sets all unselected dimensions' drill order to 0 and then attempts to add 1 to the drill order of all selected fields.  The theory is that a selected field increments in the order of its selection.  So, the first field to be selected will always have the highest Drill Order.  The last, the lowest.

           

          sub DrillTo
          ' Set Drill Order of unselected fields to 0
          set p = ActiveDocument.Fields("DrillField").GetAlternativeValues
          for i = 0 to p.Count-1
          set Result = ActiveDocument.DynamicUpdateCommand("UPDATE * SET DrillOrder = 0 WHERE DrillField='"&p.Item(i).Text&"'")
          next

          ' Increase Drill order for all selected fields
          set s = ActiveDocument.Fields("DrillField").GetSelectedValues
          for i = 0 to s.Count-1
          MsgBox(s.Item(i).Text)
          set Result = ActiveDocument.DynamicUpdateCommand("UPDATE * SET DrillOrder = DrillOrder + 1 WHERE DrillField='"&s.Item(i).Text&"'")
          next

          ' Get Selected values in Drill Order
          ' Add to Pivot using AddDimension()
          end sub

           

           

          This really isn't working though.  The second DynamicUpdate doesn't seem to work becaue the listbox which is ordered by DrillOrder and is displaying the value of the field, does not change.  Can anyone tell me if there is a better way to do this?

          Thank you.

          James

          • Re: Listbox selection order

            After a lot of banging my head against a wall, my macro is working.  I have Qlikview dynamically drilling in a pivot so that the end user just needs to check and/or uncheck any dimensions in any order and the pivot will update to breakdown the numbers in the same order.

            • Listbox selection order

              I took the Film Example document and added my Drill table and macro to it.  Hopefully, it works for everyone.  There are lots of extras that could be added to it such as automatically adjusting the column width when a dimension is added or any other Dimension property that exists for that matter.  I would just add a column in the DrillPaths table to store that information and then just adjust the macro to apply it when loaded.

               

              Of course, this currently only works on a single Pivot table but it could be expanded to work on any pivot table and any set of dimensions.  If anyone has any suggestions, on how to do this better, please let me know.  This was my first macro which was made for my first full project in Qlikview so go easy on me.

               

              http://myweb.dal.ca/jrozee/Films_DynamicDrill.qvw

               

              Thanks.

               

              James