topic Re: Percentage in Column in QlikView

Percentage in Column

upaliwije — Wed, 20 Aug 2014 15:30:18 GMT

I have following pivot table where I want to calculate the percentage as given below in the last column

but when I write the following expression desired result is not shown in the % marked in red

My expression is COUNT(NOS)/COUNT(TOTAL NOS)

Pls help me rectify my expression

	PAID_YEAR	2011	2011	2011
AGENT_GROUP	AGENT_PAYEE	PAID_AMOUNT	NOS	%	Expected %
AGENT	AMW	13,908,266	332	0.41%	2%
AGENT	CARMART_LANKA	4,525,290	27	0.03%	0%
AGENT	COLONIAL MOTORS	191,827	2	0.00%	0%
AGENT	ABANS	294,065	7	0.01%	0%
AGENT	DAVID PEIRIS	575,574	12	0.01%	0%
AGENT	Total	19,495,022	380	0.47%	2%
OTHERS	OTHERS	656,896,142	19962	24.58%	98%
OTHERS	Total	656,896,142	19962	24.58%	98%
Total		676,391,164	20297	25.00%	100%

Re: Percentage in Column

Wed, 20 Aug 2014 15:33:55 GMT

You the Relative Property in the expression(this calculates the Percentage).

Also, Can you also look into 'Show in Percent(%)' option in the Number Format Settings in the Number Tab of the Properties

-Ram

Re: Percentage in Column

JonnyPoole — Wed, 20 Aug 2014 15:34:04 GMT

replace: COUNT(NOS)/COUNT(TOTAL NOS)

with: Sum(NOS) / Sum( Total NOS)

Re: Percentage in Column

upaliwije — Wed, 20 Aug 2014 15:43:00 GMT

NOS IS NOT A NUMERIC VALUE BUT A STRING

Re: Percentage in Column

upaliwije — Wed, 20 Aug 2014 15:43:33 GMT

WHAT YOU SAY IS NOT CLEAR TO ME

Re: Percentage in Column

Anonymous — Wed, 20 Aug 2014 15:46:59 GMT

then try the num#() function...something like -

sum(num#(NOS))/sum(total num#(NOS))

Re: Percentage in Column

upaliwije — Wed, 20 Aug 2014 15:52:58 GMT

IT IS NOT WORKING

MY NOS IS LIKE THIS KH1A34222

Re: Percentage in Column

pgalvezt — Wed, 20 Aug 2014 15:53:59 GMT

Hi,

Try with Only or MaxString Functions ()

Re: Percentage in Column

Anonymous — Wed, 20 Aug 2014 15:56:35 GMT

they are not like that in your post. can you provide correct formats for your fields? if the formats you provide are not correct, solutions will not work either.

Re: Percentage in Column

upaliwije — Wed, 20 Aug 2014 15:57:19 GMT

I cant understand it . Pls write the full expression

Re: Percentage in Column

upaliwije — Wed, 20 Aug 2014 16:01:01 GMT

My NOS format are like as follows

CLNU1A11000023

CLNU1H11000009

CLNU1A11000079

CLNU1A10000699

CLNU1C10000286

CLK81A10000030

CLNU1A11000038

So my expression is count(nos)

Re: Percentage in Column

antoniotiman — Wed, 20 Aug 2014 16:29:26 GMT

Hi Upali,

try

Count(NOS)/RangeSum(Top(Column(N),1,NoOfRows()))

where N=number of column NOS

Regards,

Antonio

Re: Re: Percentage in Column

pgalvezt — Wed, 20 Aug 2014 16:35:44 GMT

Is this what you want?

Re: Percentage in Column

upaliwije — Wed, 20 Aug 2014 17:14:04 GMT

Thanks all