topic Re: How to add a calculated dimension to a chart from a VBScript macro ? in QlikView

How to add a calculated dimension to a chart from a VBScript macro ?

Wed, 01 Jun 2011 14:07:48 GMT

Hi all,

I need to create a bunch of graphs and wanted to quickly write a little macro that would replicate the same layout for all the required graphs.

So I started to write some lines of code but quickly hit a problem. Here is the (simplified) code:

Function CreateGraphs()
  Set chart = ActiveDocument.ActiveSheet.CreateLineChart
  chart.AddDimension "=IF([DeterminationCode]='XYZ', [SamplingTime])"
  chart.AddExpression "MeasurementValue"
End Function

For some reason, this line "chart.AddDimension" is silently ignored. If I add the same calculated dimension interactively: no problem, all is working perfectly. But I'm unable to reach the same result via the macro.

It looks like this issue has already been raised in a previous post but I could find the right way to workaround the issue.

Any suggestion to help me?

Thanks a lot, have a great day.

Gaëtan

Re: How to add a calculated dimension to a chart from a VBScript macro ?

Thu, 02 Jun 2011 05:14:56 GMT

Hi Gaetan,

as far i can see AddDimension with calculated dimension is not working on line/bar charts but works if pivot table is created. So my idea is macro first create pivot table with calculated dimension and then change the new created chart type to line chart. The folloing macro do the job:

sub CreateGraphs
    set chart = ActiveDocument.ActiveSheet.CreatePivotTable
    set s=ActiveDocument.Sheets("Main")
    ' loop through all sheet objects to find the chart ID of the new created chart
    for i=0 to s.NoOfSheetObjects-1
         objid = s.SheetObjects(i).GetObjectId
        ' filter only chart objects because we create chart 
        if left(replace(objid,"Document\",""),2) = "CH" then
             newchart = s.SheetObjects(i).GetObjectId
             newchart = replace(newchart,"Document\","")
          end if
     next
     set linechart = ActiveDocument.GetSheetObject(newchart)
     'add dimensions and expressions. At this point the chart is still pivot table
     linechart.AddDimension "=IF(Name='A','AA','Name')"
     linechart.AddExpression "count(id)"
     'change the chart type to line chart
     linechart.SetChartType  4   ' line
End sub

Hope this helps!

Stefan

Re: How to add a calculated dimension to a chart from a VBScript macro ?

Thu, 02 Jun 2011 10:07:36 GMT

Hello Stefan,

Thanks for your help, it indeed looks like a good idea but according to the first tests, I can add the calculated dimension to the pivot tabe (that's a first good news!) but the SetChartType operation does not work...

Here is the code:

Set chart = ActiveDocument.ActiveSheet.CreatePivotTable
chart.AddDimension "=IF([DeterminationCode]='XYZ', [SamplingTime])"
chart.AddExpression "MeasurementValue"
If chart.SetChartType(4) Then
  MsgBox "ok"
End If

The message "ok" never appears and I see a pivot table with the correct dimension/expression. If I then change the type "manually", it just works fine.

What can be wrong?

Re: How to add a calculated dimension to a chart from a VBScript macro ?

Thu, 02 Jun 2011 10:24:47 GMT

Hi Gaetan,

which version of qv did you use? I'm working on v9 and the code works

Regards!

Stefan

Re: How to add a calculated dimension to a chart from a VBScript macro ?

Thu, 02 Jun 2011 10:28:47 GMT

Hi again,

Got it to work...The trick is to add the calculated dimension to a pivot chart, then allow "fast change" to line chart before calling SetChartType. See code below.

Thanks for the top Stefan, it really helped.

' Create new chart

' Note: adding calculated dimension does not work on line charts so let's start

' with a pivot table and change the type later

Set pchart = ActiveDocument.ActiveSheet.CreatePivotTable

' Get the object ID

pchartid = Replace(pchart.GetObjectId, "Document\", "")

' Add calculated dimension

pchart.AddDimension "=IF([DeterminationCode]='XYZ', [SamplingTime])"

' Add expression

pchart.AddExpression "MeasurementValue"

' Now switch to a "line chart" type; to do so, we first

' need to allow "FastChange"

set p = pchart.GetProperties

p.GraphLayout.FastChange.Line = true

pchart.SetProperties p

' Do change type to Line chart

pchart.SetChartType 4

' Now retrieve the line chart object

Set chart = ActiveDocument.GetSheetObject(pchartid)

Set p = chart.GetProperties

' Revert the "fastchange" trick to be able to change the type of the graph

p.GraphLayout.FastChange.Line = false

' **

' ** Do your own customization of the line chart object here **

' **

' Apply your change

chart.SetProperties p

Re: How to add a calculated dimension to a chart from a VBScript macro ?

Thu, 02 Jun 2011 10:40:08 GMT

Thanks. I'll have it in mind 🙂

Re: How to add a calculated dimension to a chart from a VBScript macro ?

Mon, 06 Jun 2011 07:35:44 GMT

Hi Stefan,

Stefan Stoichev wrote:
which version of qv did you use? I'm working on v9 and the code works

Indeed, I should have mentionned it : I'm using V10 SR2.

Again, thanks for your precious help. Now I'm looking for a solution to change the X-axis to a continous one, It does not look to be exposed in the API, ...? I may need to create a new thread about this one...

Have a great day.

Re: How to add a calculated dimension to a chart from a VBScript macro ?

Mon, 06 Jun 2011 09:07:41 GMT

Hi Gaetan,

and i can see the how countinuos in the API

Regards!

Stefan

Re: How to add a calculated dimension to a chart from a VBScript macro ?

Mon, 06 Jun 2011 16:00:26 GMT

Stefan,

Stefan Stoichev wrote:
and i can see the how countinuos in the API

What do you mean? Do you know how to force a "continous X-axis" in a VBScript macro?

Have a great evening.

Gaëtan

Re: How to add a calculated dimension to a chart from a VBScript macro ?

Mon, 06 Jun 2011 16:12:47 GMT

Sorry Gaetan it was typo and i mean: can't 😞

How to add a calculated dimension to a chart from a VBScript macro ?

Thu, 09 Jun 2011 15:25:29 GMT

Stefan,

Thanks for your help, I finally found it:

chart.ChartProperties.NumericX = True

Have a wonderful day.