related to macro for a combo chart

[vvvvvvizard]

I successfully created a combo chart using a macro

both expressions are bars , how do i set one expression as a Bar and the other as a line in the same chart.

Below is the code used

Sub GenerateComboChart()

Dim myChart

' Create a new Bar Chart

Set myChart = _

ActiveDocument.ActiveSheet().CreateComboChart

' Add a dimension of Country to the new chart

myChart.AddDimension "MonthYear"

myChart.AddDimension "Alias"

myChart.AddExpression "=Avg([Saturation Index (%)])"

myChart.AddExpression "=Max([Saturation Index (%)])"

' Get the properties object

Dim cp

Set cp = myChart.GetProperties()

' Set the title of the dimension

cp.Dimensions(0).Title.v = "Alias"

' Set the Title-in-chart text

cp.ChartProperties.Title.Title.v = sitearray(0)

' Set the Window title

cp.GraphLayout.WindowTitle.v =  sitearray(0)

' Set sort by Y-Value

cp.SortByYValue = -1

' Get the expression properties

Dim expr, exprvis

Set expr = _

cp.Expressions.Item(0).Item(0).Data.ExpressionData

Set exprvis = _

cp.Expressions.Item(0).Item(0).Data.ExpressionVisual

' Set the Expression label

exprvis.Label.v = "Sales $"

' Set the "Values on Data Point" option

exprvis.NumbersOnBars = -1

' Set the number format for the expression

exprvis.NumberPresentation.Dec = "."

exprvis.NumberPresentation.Fmt = "#,##0.00"

exprvis.NumberPresentation.nDec = 2

exprvis.NumberPresentation.Thou = ","

exprvis.NumberPresentation.Type = 11 'fixed

exprvis.NumberPresentation.UseThou = 1

' Apply the modified properties

myChart.SetProperties cp

End Sub

[petter]

It is probably this you are looking for:

exprvis.ShowAsBar = false
exprvis.ShowAsLine = true
exprvis.ShowAsSymbol = true

They will set the options for display of a particular expression....

[robert_mika]

yousuf,Petter,

beside the official API guide are you using any other sources to create VBS code?

[petter]

no ... I am not ... but I do have a long experience with VBscript since it's beginnings in the 90's and I often to use VBA in for instance Excel or even C# in Visual Studio to connect to QlikView and use the intellisense help with the object model I can get from there. To get that you have to do "early binding" to the QlikView object ...

[vvvvvvizard]

i uploaded the qvw to the orignal post , its not working

[vvvvvvizard]

just the api guide and a macro i came across in the "qlikview developer cookbook " . I found the api guide almost impossible to understand  .  If anyone has further links or resources il be thankfull.

[robert_mika]

Thanks Petter.I'm  trying establish a way to get all properties of all object in Qlikview.

API is useful but is not giving everything.

Do you know a way to achieve that?

[petter]

have you tried to create the prj folder to get QlikView to automatically export all of its document things to different xml-files (except the real data).... that should be parseable XML that you can piece together ...

[robert_mika]

Do you have an example how to do that?

[petter · Accepted solution]

You have an undefined array that makes your function stop midway and not be able to set the properties later in the function... the sitearray(0) that you reference twice is not in scope ... I have a slightly modifed hack underneath ... have a look:

Option Explicit


Sub GenerateBarChart()

Dim myChart
' Create a new Bar Chart
Set myChart = _
ActiveDocument.ActiveSheet().CreateComboChart
' Add a dimension of Country to the new chart
myChart.AddDimension "MonthYear"
myChart.AddDimension "Alias"


myChart.AddExpression "=Avg([Saturation Index (%)])"
myChart.AddExpression "=Max([Saturation Index (%)])"
' Get the properties object
Dim cp
Set cp = myChart.GetProperties()
' Set the title of the dimension
cp.Dimensions(0).Title.v = "Alias"
' Set the Title-in-chart text
cp.ChartProperties.Title.Title.v = "ABC"     ' sitearray(0)
' Set the Window title
cp.GraphLayout.WindowTitle.v = "DEF"      'sitearray(0)
' Set sort by Y-Value
cp.SortByYValue = -1
' Get the expression properties
Dim expr, exprvis
'Set expr = cp.Expressions.Item(0).Item(0).Data.ExpressionData
Set exprvis = cp.Expressions.Item(0).Item(0).Data.ExpressionVisual
' Set the Expression label
exprvis.Label.v = "Sales $"
' Set the number format for the expression
exprvis.NumberPresentation.Dec = "."
exprvis.NumberPresentation.Fmt = "#,##0.00"
exprvis.NumberPresentation.nDec = 2
exprvis.NumberPresentation.Thou = ","
exprvis.NumberPresentation.Type = 11 'fixed
exprvis.NumberPresentation.UseThou = 1
exprvis.ShowAsBar = false
exprvis.ShowAsLine = true
exprvis.ShowAsSymbol = true

'expr.ShowAsBar = false
'expr.ShowAsLine = true
'expr.ShowAsSymbol = true
' Apply the modified properties
myChart.SetProperties cp

End Sub