4 Replies Latest reply: Mar 18, 2012 3:14 AM by Sajeevan Govindan

# Calculate percentage values in pivot table

Hi,

I would like to calculate a percentage value within a pivot tabel.

We have done this before by creating a variable containing the total value and used it as the divisor in the expression.

But maybe there is a better way to do this.

My Pivot Table should look similar to this one (right now only the exp1 columns are filled):

 Month Jan Feb KPI Exp1 Exp2 % Exp1 Exp2 % KPI1 10 60 16,6666667 20 90 22,2222222 KPI2 20 60 33,3333333 30 90 33,3333333 KPI3 30 60 50 40 90 44,4444444 Total 60 100 90 100

How can I calculate the Exp2 and % columns in QlikView?

I have tried to use this formular:

=SUM([Values]) / SUM(Total [Values])

But if I do so I will get the same values for all Exp2 columns and not with reference to the month dimension.

That means if I make a "Total" all dimensions will be ignored.

How can I ignore the KPI column on the first hand and reference to the month column on the other hand?

Any suggestions?

Simon

• ###### Calculate percentage values in pivot table

OK I found the solution

The formular must be:

SUM([Values]) / SUM(Total <[Month]> [Values])

It is also possible to do a set analysis with this:

SUM({<[Country]={'DE'}>}[Values]) / SUM({<[Country]={'DE'}>}TOTAL <[Month]> [Values])

This will limit the country field to Germany and will make a total over all dimension in chart except the month.

• ###### Re: Calculate percentage values in pivot table

Hi Simon,

Sum(Values) / aggr(NODISTINCT sum(Values),Month)

Regards,

Sajeevan

• ###### Calculate percentage values in pivot table

Hi,

i think you can solve my problem again.

i've dimemsions dim1,dim2,dim 3..in hierarchial format as in pivot table.

i've a value column in the table, as one expr....i'm showing sum(value) and i also have dimension due date....& if the due date <today() date then in the expr column Overdue Amt i simply show sum(value) else i'll show it the Not due column...this is fine....

now i've to find %Overdue & %Not due.....wich is Overdue Amt/sum(value) & Not Due Amt/sum(value) respectively...

i'm getting the % fine..for each row...but at the total level i dnt want to see sum of all % for Overdue & %Not Due

Bt only the sum of Overdue Amt/sum of Remainin Amt & same for %Not due as well

Plz help

stuck for 1 day cmplete

Regards

• ###### Calculate percentage values in pivot table

Can you attach sample QV file?