topic Calculate percentage values in pivot table in QlikView

Calculate percentage values in pivot table

Wed, 02 Nov 2011 12:02:22 GMT

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

Calculate percentage values in pivot table

Wed, 02 Nov 2011 12:11:50 GMT

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

Wed, 02 Nov 2011 12:15:09 GMT

Hi Simon,

Please use the below for your 2nd expression

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

Regards,

Sajeevan

Calculate percentage values in pivot table

Fri, 16 Mar 2012 10:46:52 GMT

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

Sun, 18 Mar 2012 07:14:22 GMT

Can you attach sample QV file?