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

    Macro - BarOffsetExp doesn't work



      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.



      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

      if bDim and bExp then


      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


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


        chart.AddDimension dimSelection.Item(i).text




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


      '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




      end sub