Macro - BarOffsetExp doesn't work

Fri, 05 Apr 2013 06:29:25 GMT

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

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

'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

end sub

topic Macro - BarOffsetExp doesn't work in QlikView

Macro - BarOffsetExp doesn't work