Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

vvvvvvizard
Contributor III

related to macro for a combo chart

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

1 Solution

Accepted Solutions
MVP
MVP

Re: related to macro for a combo chart

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

13 Replies
MVP
MVP

Re: related to macro for a combo chart

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....

Re: related to macro for a combo chart

yousuf,Petter,

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

MVP
MVP

Re: related to macro for a combo chart

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
Contributor III

Re: related to macro for a combo chart

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

vvvvvvizard
Contributor III

Re: related to macro for a combo chart

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.

Re: related to macro for a combo chart

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?

MVP
MVP

Re: related to macro for a combo chart

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 ...

Re: related to macro for a combo chart

Do you have an example how to do that?

MVP
MVP

Re: related to macro for a combo chart

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

Community Browser