0 Replies Latest reply: Apr 5, 2013 2:29 AM by Dulce Sleeckx RSS

    Macro - BarOffsetExp doesn't work

      Hi,

       

      I'm trying to create a dynamic chart based in this example: http://community.qlik.com/qlikviews/1077

      Everything works fine except that I have one particular bar that needs to have a offset like the dark brown one in the picture.Does anyone have an idea on how to fix this?

       

      Improvements to the macro itself are also welcome, since I've no experience in writting macros.

       

      Thanks for your help.

      Dulce

       

      Bar offset - dark brown.PNG

      Dim vDim
      Dim vExp

      sub onAnySelection

      set x = ActiveDocument.GetCurrentSelections
      s = x.Selections
      v = x.VarId

      bDim = false
      bExp = false


      for i = lbound(s) to ubound(s)
           'msgbox v(i)&" = "&s(i)
          
           if v(i) = "%Dimension" then
          
             bDim = true
             vDim = s(i)
          
           end if
          
            if v(i) = "%Type" then
          
             bExp = true
             vExp = s(i)
          
           end if
          
      next

      if bDim and bExp then

        buildChart

      end if

      end sub

       

       

      sub buildChart

       

      set chart = ActiveDocument.GetSheetObject("CH140")

       

      'clean chart
      set dims = chart.GetProperties.Dimensions
      set exps = chart.GetProperties.Expressions
      'msgbox("Dimensions = " & dims.Count)
      'msgbox("Expressions = " & exps.Count)

       

      'Remove existing dimensions
      for i=0 to dims.Count
        chart.RemoveDimension 0
      next

       


      'add dimensions
      set dimSelection = ActiveDocument.fields("%Dimension").getPossibleValues
      for i=0 to dimSelection.Count - 1

       

           'msgbox(dimSelection.Item(i).text)
        chart.AddDimension dimSelection.Item(i).text

       

      next

       


      'removing existing expressions
      for i=0 to exps.Count - 1
        chart.RemoveExpression 0
      next

       


      'colecting expressions metadata - Formula, Name, Color, Offset Value
      set expKPI = ActiveDocument.Fields("%KPI").GetPossibleValues
      set vtype = ActiveDocument.Fields("%Type").GetSelectedValues
      set expName = ActiveDocument.Fields("%Expression").GetPossibleValues
      set expValue = ActiveDocument.Fields("%Formula").GetPossibleValues

       

      for i=0 to expValue.Count - 1

       


        chart.AddExpression expValue.Item(i).Text

       

        'Metada of the current expression
        ActiveDocument.Fields("%Formula").Select expValue.Item(i).Text

       

        set vColor = ActiveDocument.Fields("%Color").GetPossibleValues
        set vOffSet =  ActiveDocument.Fields("%Offset").GetPossibleValues

       

        'chart.AddBarOffsetExpression vOffSet.Item(0).Text     ->Doesn't work

       

        'rename an expression label
        set p = chart.GetProperties
        set exps = p.Expressions
        set expItem = exps.Item(i).Item(0).Data.ExpressionVisual
        expItem.Label.v = expName.Item(i).Text

       

        'Adding background color
        set expItem2 = exps.Item(i).Item(0).Data
        set bgExp = expItem2.AttributeExpressions.BkgColorExp
        bgExp.Definition.v = vColor.Item(0).Text

       

       

       

        chart.SetProperties p

       

      next

       

      end sub