Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vvvvvvizard
Partner - Specialist
Partner - Specialist

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
petter
Partner - Champion III
Partner - Champion III

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

View solution in original post

13 Replies
petter
Partner - Champion III
Partner - Champion III

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

yousuf,Petter,

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

petter
Partner - Champion III
Partner - Champion III

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
Partner - Specialist
Partner - Specialist
Author

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

vvvvvvizard
Partner - Specialist
Partner - Specialist
Author

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

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
Partner - Champion III
Partner - Champion III

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

Do you have an example how to do that?

petter
Partner - Champion III
Partner - Champion III

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