Is this a hard constraint on pivoting axis using macro automation?

[#1]

It took me a while to figure out how to pivot rows into columns when automatically building a new Pivot Table chart. Normally, the only way to do this is by dragging the row into a column, which changes the dimension from being on the vertical axis, to the horizontal axis.

The trick (it would appear) is to use the NumberOfLeftDimensions property. By default, this property is set to the number of dimensions in the Pivot Table. If you set this property to be an explicit value - e.g. 1, then all dimensions after the first dimension will be placed horizontally instead of vertically. Furthermore, if you increase the "NumberOfLeftDimensions" property to be greater than the number of dimensions, then this has the effect of moving expressions from columns to rows.

Therefore it is possible to have near complete control over the Pivot Table axis layout.

That notwithstanding, there is one scenario where I have not been able to figure out a way of controlling the layout through automation. Namely, I cannot find a way to tell the Pivot Table that I want to keep one or more expressions on the vertical axis, but keep one or more of my dimensions on the horizontal axis. It is possible to move expressions to my axis ,but only if all of my dimensions are already on the vertical axis. In other words, I cannot mix dimensions and expressions on my vertical axis, while having a dimension on the horizontal axis. But this is possible through mouse dragging.

Any ideas?

[michael_anthony]

Hi Neil,

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?

Regards,

Michael

[johnw]

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.

[#4]

I'm new at Qlikview and came across your post, I was wondering if you could tell me where I could find the "NumberOfLeftDimensions" property?

Thank you.

[#5]

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 Sheet=ActiveDocument.CreateSheet
set Hlo=Sheet.CreatePivotTable
Hlo.AddDimension "$Field"
Hlo.AddDimension "$Table"
Hlo.AddExpression "Only([$Field])"
set Prop = Hlo.GetProperties
Prop.TableProperties.PivotAlwaysFullyExpanded=true
Prop.Dimensions.Item(0).SortCriteria.SortByAscii=0
Prop.Dimensions.Item(0).SortCriteria.SortByLoadOrder=1
Prop.Dimensions.Item(1).SortCriteria.SortByAscii=0
Prop.Dimensions.Item(1).SortCriteria.SortByLoadOrder=1
Prop.TableProperties.NumberOfLeftDimensions = 1
Hlo.SetProperties Prop

[#6]

Hello,

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?

Thanks.

[#7]

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)
 chrtOrgQuestions.detach
 RebuildOrgChartDimensions chrtOrgQuestions, iShortestPath
 chrtOrgQuestions.attach

sub RebuildOrgChartDimensions (chrtCur, iShortestPath)
 dim propChart
 dim dimsChart
 dim asSecondaryDimensions
 dim i
 dim iOrgRollupFieldLen
 dim iCurSecondaryDimension
 dim dimCur, fldCur
 dim iDimIdx
 dim dimSG1_QShortDesc2
 dim iSG1_QShortDesc2_dim_idx


 iCurSecondaryDimension = 0
 iOrgRollupFieldLen = len(csOrgRollupField)
 redim asSecondaryDimensions(0)


 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
 end if


 chrtCur.RemoveDimension(0)
 next


 '2. Add back all ORG dimensions
 for i = 1 to iShortestPath
 chrtCur.AddDimension csOrgRollupField & i
 next


 '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
 end if
 next
 end if



 '****WARNING WARNING DANGER DANGER
 'It is necessary to refresh the chart properties object reference before pivoting
 set propChart = chrtCur.GetProperties
 with propChart
 .TableProperties.NumberOfLeftDimensions = iShortestPath

 end with
 chrtCur.SetProperties propChart

end sub

[#8]

Thanks for the prompt reply. I too had tried to use Calculated Dimensions rather than macro script but could not find a way to get the sorting right as you can't use things like Sort by Expression or Sort by Load Order with Calculated Dimensions.

[#9]

You can't explicitly set the sort order, but I've found that by default it sorts by Load Order.
So, depending on what you're doing, it may be possible to control sort order of Calculated Dimension by loading it in the order you want.

[#10]

Hi Neil,

did you find a solution for mixing dimensions and expressions on vertical axis, while having a dimension on the horizontal axis? Thanks for you answer.