topic how to move dimensions using macro? in QlikView

how to move dimensions using macro?

Wed, 10 Aug 2011 22:38:52 GMT

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?

Thu, 11 Aug 2011 01:50:12 GMT

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?

Thu, 11 Aug 2011 13:05:39 GMT

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?

Tue, 16 Aug 2011 13:19:40 GMT

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

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?

Fri, 19 Aug 2011 12:37:17 GMT

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?

Fri, 19 Aug 2011 12:38:41 GMT

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?

Fri, 02 Dec 2011 03:08:56 GMT

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?

Thu, 09 Feb 2012 16:34:31 GMT

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