topic Re: Percentage in Pivot Table in QlikView

Percentage in Pivot Table

upaliwije — Sun, 12 Oct 2014 11:18:10 GMT

I have created Pivot Table as follows

DESCRIPTION	ACTUAL 2014	ACT_RATIO	ACTUAL 2013	ACT_RATIO
SALES	7,332,885	100	967,954	100
LABOUR	339,527	7	222,262	7
MATERIAL	831,338	14	338,969	14
COMMISSION	890,125	12	75,000	12
GROSS_PROFIT	5,271,896	68	331,722	68
STAFF_EXP	853,494	11	40,000	11
ADMIN_EXP	857,451	11	80,000	11
SELLING_EXP	509,639	7	100,000	7
TOTAL_EXPENSES	2,220,584	29	220,000	29
OTH_INCOME	40,000	1	40,000	1
NET_PROFIT	3,091,312	39	151,722	39

I have calculated Actual Ratio bases on the Sales Value of Each Year for 2014 and 2013.But The calculation returns the same ratios for both years . Kindly have look at my QV document and help me to correct the expression to suit my requirement

Re: Percentage in Pivot Table

jyothish8807 — Sun, 12 Oct 2014 11:21:27 GMT

Hi Upali,

Can you share your expression you are using? Personal edition issue.

Regards

Re: Percentage in Pivot Table

upaliwije — Sun, 12 Oct 2014 11:26:50 GMT

The below is my expression

Sum(ACTUAL)/$(=$(=vActual))*100

vActual=sum({$<DESCRIPTION={'SALES'}>}ACTUAL) is my variable

Re: Percentage in Pivot Table

jyothish8807 — Sun, 12 Oct 2014 11:27:47 GMT

Hi Upali,

Rename in the field in your budget table:

load

year,

month,

sales as bud_sales,

labour as bud_labour,

from <>

In exp:

Sum(sales)/sum(bud_sales)

Use your condition which ever you are using.

Hope it will work.

Regards

Re: Percentage in Pivot Table

upaliwije — Sun, 12 Oct 2014 11:33:29 GMT

Thanks

Budget Sales has nothing to with the Actual sales. What I want to calculate the Ratio of Labour Material etc against Sales

Re: Re: Percentage in Pivot Table

jyothish8807 — Sun, 12 Oct 2014 11:49:58 GMT

Hi Upali:

try this:

exp:

sum([ACTUAL 2014])/sum(TOTAL([ACTUAL 2014]))

In number tab:

Select integer

show in percentage,

PFA

Regards

Re: Percentage in Pivot Table

upaliwije — Sun, 12 Oct 2014 11:58:04 GMT

Thanks for your help

But this not what I am Expecting

SALES	7,332,885	100	967,954	100
LABOUR	339,527	5	222,262	23
MATERIAL	831,338	11	338,969	35

My requirement is as follows

5%=(339527/7332885)*100

23%=(222262/964954)*100

Re: Percentage in Pivot Table

upaliwije — Sun, 12 Oct 2014 12:20:26 GMT

Pls help me a an Answer

Re: Percentage in Pivot Table

jyothish8807 — Sun, 12 Oct 2014 12:59:50 GMT

Hi Upali,

for Material do you want like this?

(831,338/7,332,885)*100

Regards

Re: Percentage in Pivot Table

upaliwije — Sun, 12 Oct 2014 13:02:47 GMT

yes that is correct

Re: Re: Percentage in Pivot Table

jyothish8807 — Sun, 12 Oct 2014 13:16:53 GMT

Hi,

PFA

Re: Percentage in Pivot Table

upaliwije — Sun, 12 Oct 2014 14:30:50 GMT

Hi Friends

Pls help me with a answer to my query

Re: Percentage in Pivot Table

jyothish8807 — Sun, 12 Oct 2014 14:38:44 GMT

Is the above attached is not working?

Regards

Re: Percentage in Pivot Table

upaliwije — Sun, 12 Oct 2014 14:43:33 GMT

It is working. But I have to create variable for each year. I have more than two years I want create one variable and change it as and when user select year