topic Re: expression for percentage in QlikView

expression for percentage

manoranjan_d — Tue, 30 Aug 2016 06:47:19 GMT

=num(sum(AMT)/Sum(totalAMT),'##0%')

in this scenraio i want to avoid the null value in the denominator for the expression , since dept field have some null value. how to avoid the null in expression

Re: expression for percentage

tresB — Tue, 30 Aug 2016 06:52:40 GMT

What is your expected output?

Re: expression for percentage

kaushiknsolanki — Tue, 30 Aug 2016 06:58:57 GMT

Hi,

Find the Attachment.

Regards,

Kaushik Solanki

Re: expression for percentage

Anonymous — Tue, 30 Aug 2016 07:02:44 GMT

Two options:

1) Restrict AMT null values at ETL/Script level

Load

AMT

From Table where AMT<>Null or len(AMT)>0;

Did you try like this?

=num(sum(if(len(AMT)>0,AMT))

sum( Total if(len(AMT)>0,AMT))

,'##0%')

Re: expression for percentage

Chanty4u — Tue, 30 Aug 2016 07:04:03 GMT

try

=num(sum(AMT)/Sum(total IsNull(AMT)),'##0%')

Re: expression for percentage

tamilarasu — Tue, 30 Aug 2016 07:04:59 GMT

Hi Manoranjan,

You can try,

=Num(Sum(amount)/Sum(TOTAL If(Len(Trim(dept))>0, amount)),'##0%')

Re: expression for percentage

Anonymous — Tue, 30 Aug 2016 07:06:06 GMT

Suresh/Chanty,

Does ISnull() work like u mentioned?

I think, it should come with IF(), is it not?

Re: expression for percentage

Chanty4u — Tue, 30 Aug 2016 07:07:00 GMT

yes Raj.

If(

IsNull(Expr1) = 0 AND IsNull(Expr2) = 0 AND IsNull(Expr3) = 0 ...etc.,

True(),

Null()

)

Re: expression for percentage

Chanty4u — Tue, 30 Aug 2016 07:10:08 GMT

if not try

=num(sum(AMT)/Sum(total if(IsNull(dept), AMT) )AMT),'##0%')

Re: expression for percentage

Anil_Babu_Samineni — Tue, 30 Aug 2016 07:20:35 GMT

=num(sum(AMT)/Sum(total AMT),'##0%')

Is AMT Field is There?

=num(sum({<dept = {'*'} - {'NA'} >} AMT)/Sum(total {<dept = {'*'} - {'NA'} >} AMT),'##0%')

OR Else, Can you let me know what you want to Achieve?

Re: expression for percentage

manoranjan_d — Tue, 30 Aug 2016 07:40:56 GMT

I want to avoid the Null as well as the NA in the demonitor , since dept have null as well as the Na value?

Re: expression for percentage

kaushiknsolanki — Tue, 30 Aug 2016 07:42:05 GMT

Hi,

Have a look at the application attached in my earlier post.

Regards,

Kaushik Solanki

Re: expression for percentage

manoranjan_d — Tue, 30 Aug 2016 07:45:35 GMT

amount	dept	presentabsent	SECTION	student
100	cse	absent	SEC9	1
100	cse	absent	SEC10	1
100	cse	Present	SEC2	1
100	ece	absent	SEC35	1
100	ece	absent	SEC40	1
100	ece	Present	SEC3	1
100	ece	Present	SEC34	1
100	IT	absent	SEC4	1
100	IT	absent	SEC5	1
100	IT	Present	SEC1	1
100	NA	absent	SEC35	1
100	NA	absent	SEC40	1
100	NA	Present	SEC3	1
100	NA	Present	SEC34	1
100		absent	SEC35	1
100		absent	SEC40	1
100		Present	SEC3	1
100		Present	SEC34	1

now i want to calculate the amount percentage as per the dept wise, so i n the dept we have null and NA values

so while making calculation how to avoid the dept of null and NA?

i m normally using =num(sum(AMT)/Sum(totalAMT),'##0%') so this will take the Null as as well as the NA value, so i want to avoid the NUll and NA in the calculation part

how to do this

Re: expression for percentage

manoranjan_d — Tue, 30 Aug 2016 07:47:24 GMT

as per the demanitor part Sum(totalAMT),'##0%') this will take over all right, so i wan to avoid the Null and NA in this demanitor ?

Re: expression for percentage

kaushiknsolanki — Tue, 30 Aug 2016 07:49:19 GMT

Posting code here, in case you are unable to open the application.

Add below 2 lines in script.

NullAsValue *;

Set NullValue = 'NA';

Then update your chart expression as.

=num(sum(amount)/Sum({<dept -= {"NA"}>}total amount),'##0%')

Regards,

Kaushik Solanki

Re: expression for percentage

manoranjan_d — Tue, 30 Aug 2016 07:51:21 GMT

no as per the requirement i cant do that, pls ust give me idea in the calculation part

Re: expression for percentage

tamilarasu — Tue, 30 Aug 2016 07:53:16 GMT

Hi,

Try this,

=Num(Sum(amount)/Sum(TOTAL If(Len(Trim(dept))>0 and dept<>'NA', amount)),'##0%')

Re: expression for percentage

Anil_Babu_Samineni — Tue, 30 Aug 2016 07:55:16 GMT

Try This

=Num(Sum(amount)/Sum(TOTAL If(Len(Trim(dept),'',dept)>0 and dept -= {'NA'},amount)),'##0%')

=if(your expression=0,'',your expression)

Re: expression for percentage

kaushiknsolanki — Tue, 30 Aug 2016 08:05:41 GMT

I can see the duplicate data.

Can you tell us what output you are expecting?

If it is as below.

Then use below expression.

Sum(Aggr(sum(Distinct amount),SECTION)) / Sum(Total Aggr(sum(Distinct amount),SECTION))

Regards,

Kaushik Solanki