6 Replies Latest reply: Feb 22, 2012 5:13 AM by Deepak Vadithala RSS

    Pivot table - dynamic sorting using variables - identifying # dimensions in chart

    Richard Sheppard

      Hi All,

       

      Am currently trying to build a user defined sorting option within a pivot table using variables.

       

      ie I would like the user to be able to define which expression (volume, value, delta to previous year etc...) they would like the data to be sorted (ascending / descending) by, by using variables.

      For a static report (ie one where the number of dimensions included are known), I have managed to get this to work, through re-engineering a solution posted on these forums.

       

      Due to the fact that I want to combine this functionality within a dynamic report, where the number of dimensions will vary depending on user selection, I need to edit my macro so that it recognises that there are X dimensions, and therefore sorts all X dimensions by the user selection...

       

      For some reason, not all my code is being returned when I attempt to copy and paste into Internet Explorer, but is similar to the below:

       

      sub SortMultiple

      set chart = ActiveDocument.GetSheetObject("DynamicReport2")

      set r = ActiveDocument.Variables("SortName")

      set s = ActiveDocument.Variables("SortExpression")

      set prop = chart.GetProperties

       

       

      prop.Dimensions(0).SortCriteria.SortByNumeric = 0

      prop.Dimensions(1).SortCriteria.SortByNumeric = 0

       

      if s.GetContent.string = "Value" then

      prop.Dimensions(0).SortCriteria.Expression.v "=sum(Euros)"

      prop.Dimensions(1).SortCriteria.Expression.v "=sum(Euros)"

      else

      prop.Dimensions(0).SortCriteria.Expression.v "=sum(QtyKG)"

      prop.Dimensions(1).SortCriteria.Expression.v "=sum(QtyKG)"

      end if

       

      if r.GetContent.string = "Desc" then

      prop.Dimensions(0).SortCriteria.SortByExpression = 1

      prop.Dimensions(1).SortCriteria.SortByExpression = 1

      chart.SetProperties prop

      r.SetContent "Asc", true

      else

      prop.Dimensions(0).SortCriteria.SortByExpression = -1

      prop.Dimensions(1).SortCriteria.SortByExpression = -1

      chart.SetProperties prop

      r.SetContent "Desc", true

      end if

      end sub

       

       

      Would like to adapt the above formula so that it returns "prop.Dimensions(i)" and then loops around for the number of Dimensions ( i), but unsure how to write this...?

       

      Anyone with any thoughts, greatly appreciated!

       

      Kind regards,

       

      Rich

        • Pivot table - dynamic sorting using variables - identifying # dimensions in chart
          Richard Sheppard

          could someone kindly help me with my VBA syntax....?

           

          Kind regards,

           

          Rich

            • Re: Pivot table - dynamic sorting using variables - identifying # dimensions in chart
              Deepak Vadithala

              Hi Rich,

               

              Here you go... I hope this what you needed.

               

              The below procedures calculates the number of dimensions and assigns the count to variable MyDim. You can take this two step further, I mean you can do the same for experssion and dynamically count and add the experssion for sorting. Then you can change the code to generic code so that it works with any chart intead of using the chart name as literal.


              '----------------------------------------- Parent Procedure ----------------------------------------------

              Public Sub SetObjectToVariables ()

                  Set MyGraph = ActiveDocument.GetSheetObject("DynamicReport2")                    'Assigning the Chart to the variable MyGraph

                  Set MyDims = MyGraph.GetProperties.Dimensions                            'Assigning the Chart Dimension Properties to the Variable MyDims

                  Set MyExps = MyGraph.GetProperties.Expressions                            'Assigning the Chart Expression Properties to the Variable MyExps

                  Set MyGraphProp = MyGraph.GetProperties                                'Assigning the Chart Preoperties to the Variable MyGraphProp

              End Sub

              '--------------------------------------------------------------------------------------------------------------------

               

              '----------------------------------------- Calling Procedure ----------------------------------------------

              Sub SortName

                  set chart = ActiveDocument.GetSheetObject("DynamicReport")

                  set r = ActiveDocument.Variables("SortName")

                  set s = ActiveDocument.Variables("SortExpression")

                  set prop = chart.GetProperties

               

               

                  prop.Dimensions(0).SortCriteria.SortByNumeric = 0

               

                  if s.GetContent.string = "Value" then

                  prop.Dimensions(0).SortCriteria.Expression.v = "=sum(Euros)"

                  else

                  prop.Dimensions(0).SortCriteria.Expression.v = "=sum(QtyKG)"

                  end if

               

                  if r.GetContent.string = "Desc" then

                      For i=0 to MyDims.Count       

                          prop.Dimensions(i).SortCriteria.SortByExpression = 1        'ascending

                          chart.SetProperties prop

                          r.SetContent "Asc", true

                      i = i + 1

                      Next

                  else

                      For i=0 to MyDims.Count               

                          prop.Dimensions(0).SortCriteria.SortByExpression = -1        'descending

                          chart.SetProperties prop

                          r.SetContent "Desc", true

                      i = i + 1

                      Next           

                  end if

              end sub

              '-----------------------------------------------------------------------------------------------------------------------

               

              I totally agree with Steve that is not recommended to use the VBA/VB Scripting approach but I have solved many real life problems using this VB Scripting. So I have love and hate relationship with VBA since my Excel days. I am not sure if VB Scripting in QlikView supports full OOP concepts but I am sure it support object model concept. I always create public procedure & functions outside my calling procedure and call the procedures or functions wherever required by passing the parameters. I am not sure if we can still call it inheritence in OOP concepts. I dont come from computers background so please bear with my ignorance.

               

              I hope this helps!

               

              Let me know if you need anything more in VBA. I'd love to do contribute.

               

              Good luck.

               

              Cheers - DV