How to remove negative and null values from expression

[#1]

Dear Tamil,

Here is the sample file,

Dear Experts,

COLL_CODE	LOAN_NO	NAME	AREAS	LOAN	NO_OF_INS
CHEQUE	RL0068	A.M.C.L.ATTANAYAKE	0	300000	0
CL001	RL0013	D.A.DAGLAS	22500	100000	18
	RL0035	K.LIYANARACHCHI	10000	20000	40
	RL0044	P.D.GAMAGE	22400	200000	9
	RL0050	M.A.K.I.FERNANDO	29750	150000	15
	RL0051	D.M.R.S.THENNAKOON	13500	200000	5
	RL0054	R.G.PREMACHANDRA	2500	200000	1
	RL0067	A.M.R.BANDARA	325	25000	1
	RL0069	K.P.ARIYASENA	0	50000	0
	RL0071	S.A.U.PALITHA	20	30000	0
	RL0073	J.M.DUSHANTHA	0	50000	0
	RL0074	E.G.D.M.K.PRADEEP	-475	25000	-1
	RL0075	T.G.A.LASANTHIKA	0	100000	0
	RL0076	K.U.SARANAPALA	2500	200000	1
	RL0077	G.V.C.JAYATHILAKA	-350	50000	-1
	RL0078	N.M.P.NAWARATHNE	-350	50000	-1
	RL0082	D.M.R.S.THENNAKOON	3900	70000	4
	RL0089	L.S.GAJANAYAKA	-675	150000	-0
	RL0092	J.C.KUMARI	0	100000	0
	RL0093	K.R.SILVA	-250	150000	-0
	RL0100	V.SHIVAKUMAR	0	25000	0
	Total		105295
CL002	RL0081	A.R.THILAKARATHNE	0	100000	0
	RL0085	N.SIVARAJAH	575	50000	1
	RL0086	M.KALEIRAJAH	390	30000	1
	RL0090	D.R.T.SABRIN	-1500	60000	-2
	RL0095	S.H.M.SHAKEER	-75	25000	-0
	RL0099	J.M.C.P.K.JAYATHILAKA	0	100000	0
	RL0101	R.M.SARATH	190	15000	1
	Total		-420

My pivot table shown above.

AREA = SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT))))

LOAN = IF(STATUS='ACTIVE', LOAN_AMOUNT)

NO_OF_INS = RANGESUM(AREAS)/INS_AMT

I want to remove the negative and null values from the resulted table. so How can I modify my expressions to do the same?

Thanks in Advance.

Priyantha.

[ajsjoshua]

Hi,

Try this one

AREA =if( SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT))))>0,SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT)))))

LOAN =if( IF(STATUS='ACTIVE', LOAN_AMOUNT)>0, IF(STATUS='ACTIVE', LOAN_AMOUNT)

NO_OF_INS =if( RANGESUM(AREAS)/INS_AMT>0,RANGESUM(AREAS)/INS_AMT)

Regards,

Joshua.

[sujeetsingh]

There are number of ways

* handling them at back end that will surely provide you a best response time and less memory utilization for the object (pivot table)

* the next way is handling such values in the expression itself like you have expression as Total Cost - Expense

  writing it as =if(Total Cost >= Expense,Total Cost - Expense)

  Or even handling it by =if(left(Total Cost - Expense)='-',null() ,Total Cost - Expense)

[avinashelite]

try like this

AREA =

if(SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT))))>0,SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT)))),null())

LOAN =

if(IF(STATUS='ACTIVE', LOAN_AMOUNT)>0,IF(STATUS='ACTIVE', LOAN_AMOUNT),null())

NO_OF_INS =

if(RANGESUM(AREAS)/INS_AMT>0,RANGESUM(AREAS)/INS_AMT,null())

Go to presentation tab and check the suppress null value  option

[tamilarasu]

Hi Priyantha,

Check the attachment.

[sasikanth]

hi,

try to use set expressions, by default set expression removes Null values

=sum({<STATUS={'ACTIVE'},FLAG={'A','R'}>} RECIEVABLE-RECEIPT_AMT)

=Sum({<STATUS={'ACTIVE'}>} LOAN_AMOUNT)

use this calculated Dimension in place of NAME dimension to remove -ve values

=if(  (aggr(sum({<STATUS={'ACTIVE'},FLAG={'A','R'}>}RECIEVABLE-RECEIPT_AMT),NAME,LOAN_NO,COLL_CODE))>0,NAME)

[#7]

Dear Joshua,

Your expression works as expected but i cant get the correct subtotal.

Thkx for the attempt.

Priyantha.

[#8]

Hi, Avinash,

Your expression works as expected but i cant get the correct subtotal.

Thkx for the attempt.

Priyantha.

[perumal_41]

Hi

Instead change all expression ,we can change  like

replace LOAN_NO by   below expression

Aggr(Only( {<LOAN_NO={"=SUM({<STATUS={'ACTIVE'}, FLAG={'A','R'} >}(RECIEVABLE-RECEIPT_AMT))>0"}>}

LOAN_NO),LOAN_NO)

and  check the suppress null values for the dimensions

[#10]

Dear Tamil,

I have only PE version, please post on the wall,

Thnx,

Priyantha.