Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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.
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.
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
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.
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
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.
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.
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.