topic Pivot table % Totals in QlikView

Pivot table % Totals

Tue, 27 Nov 2012 14:00:28 GMT

Hi all,

I have a pivot table as shown below. there is an expression which calculates the % which is working per line but the subtotals and totals are not working. (As you can see it shows 33.33% for each.

Does anyone know how i can fix this please?

The expressions used are ..

Score

Sum(Score)

Percentage

=sum(Score)/sum({<City=>}(aggr(NODISTINCT sum( Score), Country)))

Re: Pivot table % Totals

Tue, 27 Nov 2012 14:22:41 GMT

Try this:

=sum(Score)/sum(TOTAL<City> aggr(sum(Score),Continent,Country, City))

TMF

Message was edited by: tmendesf

Re: Pivot table % Totals

Tue, 27 Nov 2012 14:33:38 GMT

Hi TMF,

That works for the totals but now each line reports as 100% as below

Re: Pivot table % Totals

Tue, 27 Nov 2012 14:38:56 GMT

Try this:

=sum(Score)/sum(TOTAL<City> aggr(sum(Score),Continent,Country))

TMF

Re: Pivot table % Totals

Tue, 27 Nov 2012 14:45:24 GMT

Hi, thanks again but now im getting some lines reporting nothing..

Nottingham for example should be reporting 20/35*100 = 57.14% but is showing -

Re: Pivot table % Totals

Tue, 27 Nov 2012 14:52:20 GMT

Can you post a sample file?

Re: Pivot table % Totals

Tue, 27 Nov 2012 15:03:08 GMT

Hi, yes here is a sample

Re: Pivot table % Totals

Gysbert_Wassenaar — Tue, 27 Nov 2012 15:08:05 GMT

Try:

if(Dimensionality()=3, sum(Score)/sum(total <Country,Continent> Score),

if(Dimensionality()=2, sum(Score)/sum(total <Continent> Score),

sum(Score)/sum(total Score)))