Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

How to display partial sum/Total for fields in a dynamic report ?

Dear All,

I am creating a dynamic report using the sample posted by John Witherspoon.

I have a requirement where i have to display Total value(partial sums) for a couple of fields.

How do i go about it ? any suggestions ?

Punar

Tags (1)
4 Replies
Not applicable

How to display partial sum/Total for fields in a dynamic report ?

Hi punarkarthik,

Can you please post a sample of your requirement.. Then it will be more clear to understand and someone will help you on the issue..

Thanks and Regards,

Nilupa

Not applicable

How to display partial sum/Total for fields in a dynamic report ?

I am using the following Macro to generate dynamic reports in qlikview.

MACRO:

sub rebuild()

ActiveDocument.GetApplication.WaitForIdle 'wait for QlikView to finish before modifying report

set selectedExpressions = ActiveDocument.getField("Expressions").getSelectedValues

set selectedDimensions = ActiveDocument.getField("Dimensions").getSelectedValues

selectedExpressionMax = selectedExpressions.Count - 1

selectedDimensionMax = selectedDimensions.Count - 1

if selectedExpressionMax + selectedDimensionMax > -2 then 'bypass if nothing is selected

'--------------------------- EXPRESSIONS ------------------------------------------------------

set chart = ActiveDocument.getSheetObject("CH01")

set chartProperties = chart.GetProperties

set chartExpressions = chartProperties.Expressions

chartExpressionMax = chartExpressions.Count - 1

dim selectedExpression(100) 'runs slightly faster if first set up a simple array

for j = 0 to selectedExpressionMax

selectedExpression(j) = selectedExpressions.Item(j).Text

next

for i = 0 to chartExpressionMax 'for every possible expression defined to the chart

e = chartExpressions.Item(i).Item(0).Data

chartExpression = e.ExpressionVisual.Label.v

expression = e.ExpressionData

if chartExpression <> "Default" then

expression.Enable = false 'disable the expression

end if

for j = 0 to selectedExpressionMax 'check all selected expressions

if selectedExpression(j) = chartExpression then 'if user selected the expression

expression.Enable = true 'enable it

j = 9999 'and exit the inner loop

end if

next

next

chart.SetProperties chartProperties

'--------------------------- DIMENSIONS --------------------------------------------------------

chartDimensionMax = chart.GetColumnCount - 1

for i = chartDimensionMax to 0 step -1 'remove existing dimensions

chart.removeDimension i

next

for j = 0 to selectedDimensionMax 'add selected dimensions

chart.addDimension selectedDimensions.Item(j).Text

next

end if

end sub

This works perfectly.Now i want to display the partial sum for a couple of fields in the dynamic report.Is it possible ?



MVP
MVP

How to display partial sum/Total for fields in a dynamic report ?

Here's an example to play with. In this case, I check if there's an expression selected, and if so, I do partial sums on EVERY selected dimension. If you only want the partial sums on some of the selected dimensions, you'll need some way to identify them. There's probably a slightly cleaner way to do it, but I just stuck the extra code down at the bottom of the macro for simplicity.

if selectedExpressionMax > -1 then
set chartProperties = chart.GetProperties
for i = 0 to selectedDimensionMax
chartProperties.dimensions(i).showpartialsums = true
next
chart.SetProperties chartProperties
end if
end if
end sub

Not applicable

How to display partial sum/Total for fields in a dynamic report ?

Thanks a lot John ! This is exactly what i want.Perfect Big Smile