topic Re: Pivot Subtotals in QlikView

Pivot Subtotals

Thu, 30 May 2013 05:43:45 GMT

have a normal pivot chart, the values are correct but the subtotals of the percentages are causing problem,

This is the O/P got,

	Status	Compliance		NA		NonCompliance		Total
Process	Question	%	Count	%	Count	%	Count	%	Count
Fixing	1	82	226			18	49	100	275
	2	87	240	13	35			100	275
	3	3	9	97	266			100	275
	Total	4	475	2	301	0	49	6	825

Expression Used

% =

Count

(Compliance_Report.FILE_NO)/Count(total<Compliance_Report.QUESTION> Compliance_Report.FILE_NO

)

Count=

Count

(Compliance_Report.FILE_NO

)

have marked the subtotals Question and Status

in % compliance total, shld get 57% which is the average of the above 3 %s

in % NA total, shld get 45% which is the average of the above 3 %s

in % Noncompliance total, shld get 6% which is the average of the above 3 %s

Can pls anybody explain wht wrong i did and hw to rectify the issue,

Re: Pivot Subtotals

er_mohit — Thu, 30 May 2013 05:58:02 GMT

Tried to put this condition in sum()

sum(Compliance_Report.FILE_NO)/Count(total<Compliance_Report.QUESTION>Compliance_Report.FILE_NO

)

sum(Compliance_Report.FILE_NO

)

hope it helps

Re: Pivot Subtotals

Gysbert_Wassenaar — Thu, 30 May 2013 05:59:09 GMT

You want an average of relative counts. That's not what you specified. You only told QV to calculate the counts. Try using the aggr function to aggregate and aggregation, i.e. average a count over N dimensions:

avg(aggr(Count(Compliance_Report.FILE_NO)/Count(total<Compliance_Report.QUESTION> Compliance_Report.FILE_NO), Compliance_Report.PROCESS, Compliance_Report.QUESTION)

Compliance_Report.PROCESS is a guess on my part. Replace that with the actual dimension name.

Re: Pivot Subtotals

Thu, 30 May 2013 06:18:49 GMT

Hey Gysbert, i tried ur exp

but nt getting the desired output, many percentages are missed and those got are showing 100%.

Subtotals are correct but the % for each qtn is wrong.

I need the % to be w.r.t each qtns and then the subtotals to be showing avg of the % gt collectively.

Can u help!!

Re: Pivot Subtotals

Gysbert_Wassenaar — Thu, 30 May 2013 06:26:54 GMT

Ok, try using the dimensionality function. First add an expression with only dimensionality() so you can see what level the subtotals get. Then create an if statement using the dimensionality function to use the right expression for the right level:

if(dimensionality() = 2 , count_expression_here, avg_aggr_expression_here)

Change dimensionality() = 2 to whatever it needs to be for your chart.

Re: Pivot Subtotals

Thu, 30 May 2013 07:15:38 GMT

Thnks the first expression stated by you has worked ......

Gysbert, Can u throw some light on the dimensionality() function, i didnt understnd the functionality of this function, hw it works.