topic Re: Calculating column percentage in QlikView

Calculating column percentage

Mon, 14 Apr 2014 14:05:19 GMT

Hi, I need to calculate a percentage in a column:

Column(1) / Sum(Total <Id> Sales),

but the percentage has an error, because instead to use 34700, the value use it's 29629 (=34700 - 1538 - 3533), 1538 and 3533 are repeat (colour red and blue). but, I don't know. why happened with it? Thank you.

Id	Sales	% Correct using 34700	% Wrong using 29629
Mark1	1538	4,43%	5,19%
Mark1	5177	14,92%	17,47%
Mark1	2577	7,43%	8,70%
Mark1	3533	10,18%	11,92%
Mark1	210	0,61%	0,71%
Mark1	3894	11,22%	13,14%
Mark1	130	0,37%	0,44%
Mark1	172	0,50%	0,58%
Mark1	1538	4,43%	5,19%
Mark1	3533	10,18%	11,92%
Mark1	9200	26,51%	31,05%
Mark1	3198	9,22%	10,79%
Sum OK	34700
Sum Wrong	29629

Re: Calculating column percentage

jonathandienst — Mon, 14 Apr 2014 14:13:17 GMT

If this is a straight table, select sum of rows for the % expression.

If this is a pivot: Use this aggr expression to get the total of the percentages (as opose

Sum(Aggr(Sales / Sum(Total <Id> Sales), Id, <other dimensions>))

<other dimensions must include all the table dimensions - so if Date and Customer are other dimensions:

Sum(Aggr(Sales / Sum(Total <Id> Sales), Id, Date, Customer))

That will give you the sum of the %ages.

HTH

Jonathan

PS I am assuming that the expression for Column(1) is Sum(Sales)

Re: Calculating column percentage

MK_QSL — Mon, 14 Apr 2014 14:15:36 GMT

Check enclosed file...