I get the same problem when trying to achieve same outcome. My thinking is that there is a gap b/w the underlying table definition and what is available to the automation. With the single value NumberOfLeftDimensinos it is impossible to have some fields on the horizontal axis and expressions on the vertical.
Have you reported this to QlikTech as a bug or enhancement?
It's been a long time since I attempted something like this, but I remember having similar trouble trying to set up my horizontal and vertical dimensions and expressions using the API. I remember concluding what Michael says, that the necessary controls simply weren't available in the API. Now, I don't guarantee that I'm remembering it correctly, and even if I am, I don't guarantee that I didn't just miss something. But yeah, I couldn't find it in the API either. I didn't report it.
NumberOfLeftDimensions is a property of TableProperties. You're best to use the API Guide.qvw document to explore the OLE Automation API. I have pasted a code sample showing how it works.
rem Create HLO system table on new sheet
set Prop = Hlo.GetProperties
Prop.TableProperties.NumberOfLeftDimensions = 1
I have a similar but simpler problem, I have 1 or 2 dimensions I need to add via API that need to be at the top of a Pivot Table (header) with the expressions displayed down the side (vertically). It appears you have mastered this much but I seem to be having problems. Can you share the "NumberOfLeftDimensions" code where you had the expressions on the side and the dimensions in the header?
I've pasted a code excerpt below, that should help.
Basically the gist of it is that you need to remove all dimensions and add them back in again. I think that's the "gotcha". Also, if you use "Indent Mode", you need to turn it off while manipulating the dimensions, and then turn it back on again. Also, I recommend you detach the chart before performing these manipulations, and then reattaching the chart. Otherwise you'll likely experience lock-up.
I should also point out, that I don't use this approach any longer, and instead am using Calculated Dimensions to show/hide levels. In general, I've found that messing around with adding and removing dimensions through VB Script causes performance issues, in large part because it messes up chart caching. That said, your situation may be different.
set chrtOrgQuestions = ActiveDocument.GetSheetObject(csScoredQuestionsChartId)
RebuildOrgChartDimensions chrtOrgQuestions, iShortestPath
sub RebuildOrgChartDimensions (chrtCur, iShortestPath)
dim dimCur, fldCur
iCurSecondaryDimension = 0
iOrgRollupFieldLen = len(csOrgRollupField)
set propChart = chrtCur.GetProperties
set dimsChart = propChart.Dimensions
'1. Cache the the current secondary dimensions, while at the same time removing all dimensions
for i = 0 to dimsChart.Count - 1
' msgbox "Cur iteration: " & i
if strcomp(left(dimsChart(i).PseudoDef.name, iOrgRollupFieldLen), csOrgRollupField) <> 0 then
' msgbox "Found a difference: " & dimsChart(i).PseudoDef.name
redim preserve asSecondaryDimensions(iCurSecondaryDimension)
asSecondaryDimensions(iCurSecondaryDimension) = dimsChart(i).PseudoDef.name
iCurSecondaryDimension = iCurSecondaryDimension + 1
'2. Add back all ORG dimensions
for i = 1 to iShortestPath
chrtCur.AddDimension csOrgRollupField & i
'3. Add back the secondary dimensions
if iCurSecondaryDimension > 0 then
for i = 0 to iCurSecondaryDimension - 1
'NB: This code must be refactored to show partial sums for all non-root and non-direct-child-of-root score groups
iDimIdx = chrtCur.AddDimension(asSecondaryDimensions(i))
if strcomp(asSecondaryDimensions(i), csSG1_QShortDesc2) = 0 then
iSG1_QShortDesc2_dim_idx = iDimIdx
'****WARNING WARNING DANGER DANGER
'It is necessary to refresh the chart properties object reference before pivoting
set propChart = chrtCur.GetProperties
.TableProperties.NumberOfLeftDimensions = iShortestPath
Nope - nothing beyond NumberOfLeftDimensions.
However, if you're looking to generate document layouts ahead of time it's possible to achieve whatever layout you desire through XML manipulation. Although this won't help you when you're trying to performing interactive chart manipulations.
That said, I've since changed my approach to QlikView, and generally avoid using VB macros whenever possible to achieve my goals. Instead, it's better to drive everything by way of expressions. In some cases you can achieve your goals by pre-creating chart layouts and then using Show/Hide Expressions. However, there are limits to this approach.
thanks for your fast answer. Iam trying to save a pivot table layout, my users are building via Mouse Drag and Drop. (e.g. expressions on vertical axis, dimensions on horizontal). I save all Dimensions and Expressions in QV Variables with the help of a QV Bookmark. Whenever my users are reloading this Bookmark, their saved Pivot Table is rebuilded via VB Macros. Now i am searching for a way, so save a Drag and Drop Layout in a Bookmark.