Calculate percentage values in pivot table

[#1]

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?

Thanks in advance

Simon

[#2]

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.

[#3]

Hi Simon,

Please use the below for your 2nd expression

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

Regards,

Sajeevan

[#4]

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

[#5]

Can you attach sample QV file?