7 Replies Latest reply: Feb 9, 2012 11:34 AM by Andrew Currens RSS

    how to move dimensions using macro?

      Hello everybody. I need some help in the following. I am creating a dimesion using a macro but I do not know how to "up" the dimension. I solve this by removing some dimensions and then creating the new one and then re-creating the ones that I have deleted, but this take too much time.

       

      The example is as the following

       

      at the beginning I have the following dimensions:

                  year

                  month

                manager

                supervisor                

                 agent

       

      And when the user need some specific data I have to show the following dimensions:

                  year

                  month

                  DAY

                manager

                supervisor                

                 agent

                  

       

      As I explained before I create new dimension but this command create the new one at the final position.

       

      IMPORTANT: I use Qlikview 9 and it is a PIVOTTABLE so the MOVEDIMENSION function is not working

       

      Thanks in advanced.

        • Re: how to move dimensions using macro?

          Hi,

           

          Instead of using macros to recreate the dimension, create a cyclic group with the dimensions Manager and day in your application and ask the user to toggle between.

           

          You can create this cyclic group from the Right Click on the Pivot Table -> Dimensions tab -> on the left hand side down you will see and option to Edit Groups from there create a cyclic group.

           

          Please see the attached sample.

           

          Cheers.

            • how to move dimensions using macro?

              Thanks blackRocks but I am not solving my problem with your solution since the option are Day or nothing and not two different options in which case your cyclic solution would be satisfactory.

               

              Thanks anyway

                • Re: how to move dimensions using macro?

                  Hi,

                   

                  I am also interested in such topic. I was looking in the API Guide but can't find any solution,

                   

                  I would start by duplicating the array:

                      set chart = ActiveDocument.getSheetObject(ActiveDocument.Variables("vPivotId").GetContent.String)
                      set chartProperties = chart.GetProperties
                      set dimensions = chartProperties.Dimensions
                  set chart = ActiveDocument.getSheetObject(ActiveDocument.Variables("vPivotId").GetContent.String)
                  set chartProperties = chart.GetProperties
                  set dimensions = chartProperties.Dimensions
                  set dimensions_bkup = chartProperties.Dimensions
                  

                   

                  Then either:

                  dimensions(i) = dimensions_bkup(j)
                  

                  But QV does not accept it

                   

                  Or

                  chart.removeDimension i
                  chart.addDimension dimension_bkup(j)
                  

                  But addDimension is expecting a string, I can't figure out how to retrieve the "field name" (I'd call it more an expression) of an existing dimension.

                  I was thinking of the following attribute .AttributeExpressions.ShowValueExp.Definition.v but it is not the case, and I don't know what it refers to.

                    • Re: how to move dimensions using macro?

                      Yes, I think it is not possible to solve it since there is no function to

                      move the dimensions directly. In fact, now we are using QV 10 and still

                      there is no new function related to this topic.

                       

                       

                       

                      I solve this problem creating two pivot table, one with the following

                      dimensions:

                       

                                  year

                       

                                  month

                       

                                manager

                       

                                supervisor

                       

                                 agent

                       

                       

                       

                      and the other one including the DAY dimension:

                       

                                  year

                       

                                  month

                       

                                  DAY

                       

                                manager

                       

                                supervisor

                       

                                 agent

                       

                       

                       

                      And depending on what the user want to see is the chart that is visible.

                       

                       

                       

                      Advantage: the time for the user is satisfactory

                       

                      Disadvantage: for us, the developers, since for every change we have to

                      modify it in two charts.

                       

                       

                       

                      Thanks anyway. Marcelo

                       

                       

                      2011/8/16 n.allano <qliktech@sgaur.hosted.jivesoftware.com>

                       

                      **

                           QlikCommunity <http://community.qlik.com/index.jspa>

                      Re: how to move dimensions using macro?

                      created by n.allano <http://community.qlik.com/people/n.allano> in *Development

                      (QlikView Desktop)* - View the full discussion<http://community.qlik.com/message/141253#141253>

                       

                • Re: how to move dimensions using macro?

                  Yes, I think it is not possible to solve it since there is no function to move the dimensions directly. In fact, now we are using QV 10 and still there is no new function related to this topic.

                   

                  I solve this problem creating two pivot table, one with the following dimensions:

                              year

                              month

                            manager

                            supervisor                

                             agent

                   

                  and the other one including the DAY dimension:

                              year

                              month

                              DAY

                            manager

                            supervisor                

                             agent

                   

                  And depending on what the user want to see is the chart that is visible.

                   

                  Advantage: the time for the user is satisfactory

                  Disadvantage: for us, the developers, since for every change we have to modify it in two charts.

                  • Re: how to move dimensions using macro?
                    Ray Zhang

                    Hi, I use this method :

                     

                    when the user select diffrent condition: in macro, move all dimensions then add all needed dimensions.

                     

                    Code:

                     

                     

                     

                    SUB OnDrugAnalyzeTypeSwitch

                     

                              SET fDrugType = ActiveDocument.GetField("DrugAnalyzeType")

                              SET fValues = fDrugType.GetSelectedValues

                              SET vm=ActiveDocument.Variables("varShowLBMolicule")

                              SET vb=ActiveDocument.Variables("varShowLBBrand")

                              SET chBrandAll = ActiveDocument.GetSheetObject("CHBrandAll")

                              SET chpBrandAll=ActiveDocument.GetSheetObject("CHPBrandAll")

                     

                     

                     

                              IF fValues.Item(0).Text = "Brand" THEN

                                        vm.SetContent "0",True

                                        vb.SetContent "1",True

                                        chBrandAll.RemoveDimension 1

                                        chBrandAll.AddDimension "BrandName"

                                        ' set dimension's label name

                                        SET cp = chBrandAll.GetProperties

                                        SET dims = cp.Dimensions(1)

                                        dims.ShowLabel = True

                                        dims.Title.v="Brand"

                                        chBrandAll.SetProperties cp

                                        SET cp=Nothing

                     

                                        chpBrandAll.RemoveDimension 0

                                        chpBrandAll.AddDimension "BrandName"

                                        SET cp = chpBrandAll.GetProperties

                                        cp.TableProperties.NumberOfLeftDimensions = 2

                                        SET dims = cp.Dimensions(1)

                                        dims.ShowLabel = True

                                        dims.Title.v="Brand"

                                        chpBrandAll.SetProperties cp

                     

                                        chpBrandAll.RemoveDimension 0

                                        chpBrandAll.AddDimension "MonthYear"

                                        SET cp = chpBrandAll.GetProperties

                                        SET dims = cp.Dimensions(1)

                                        dims.ShowLabel = True

                                        dims.Title.v="Month"

                                        cp.TableProperties.NumberOfLeftDimensions = 1

                                        chpBrandAll.SetProperties cp

                     

                     

                     

                              ELSE

                                        vm.SetContent "1",True

                                        vb.setcontent "0",True

                                        chBrandAll.RemoveDimension 1

                                        chBrandAll.AddDimension "Molicule"

                                        SET cp = chBrandAll.GetProperties

                                        SET dims = cp.Dimensions(1)

                                        ' set dimension's label name

                                        dims.ShowLabel = True

                                        dims.Title.v="Molicule"

                                        chBrandAll.SetProperties cp

                                        SET cp=Nothing

                     

                                        chpBrandAll.RemoveDimension 0

                                        chpBrandAll.AddDimension "Molicule"

                                        SET cp = chpBrandAll.GetProperties

                                        cp.TableProperties.NumberOfLeftDimensions = 2

                                        SET dims = cp.Dimensions(1)

                                        dims.ShowLabel = True

                                        dims.Title.v="Molicule"

                                        chpBrandAll.SetProperties cp

                     

                                        chpBrandAll.RemoveDimension 0

                                        chpBrandAll.AddDimension "MonthYear"

                                        SET cp = chpBrandAll.GetProperties

                                        SET dims = cp.Dimensions(1)

                                        dims.ShowLabel = True

                                        dims.Title.v="Month"

                                        cp.TableProperties.NumberOfLeftDimensions = 1

                                        chpBrandAll.SetProperties cp

                              END IF

                    END SUB

                     

                      • Re: how to move dimensions using macro?

                        Thank you for this example. I was doing a similar exercise and wanted to have a button to add/remove a dimension dynamically. It seems that to identify a dimension by a name (and not an index number) you will need to have the Lable attribute populated. Once that is populated you can access the Dimension label name by using the "Title" property of the Item.

                         

                        The code i used is below if anyone's interested:

                         

                        sub toggle
                        
                        
                        
                        set chart=ActiveDocument.getSheetObject("CH01")
                        
                        set prop = chart.GetProperties
                        
                        set dims = prop.Dimensions
                        
                        
                        
                        dim exists
                        
                        dim existsIndex
                        
                        
                        
                        for i = 0 to dims.Count-1
                        
                         if dims.Item(i).Title.v = "ABC" Then
                        
                          exists = 1
                        
                          existsIndex = i
                        
                         end if
                        
                        next
                        
                        
                        
                        if exists = 1 then
                        
                          chart.RemoveDimension existsIndex
                        
                        else
                        
                          chart.AddDimension "ABC"
                        
                          set updProp = chart.GetProperties
                        
                          set updDims = updProp.Dimensions
                        
                          updDims.Item(updDims.Count-1).Title.v = "ABC"
                        
                          chart.SetProperties updProp
                        
                        end if
                        
                        
                        
                        end sub