How to remove Negative Values and null values from Expression

[#1]

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.

[tamilarasu]

May be try,

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

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

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

[Anonymous]

Maybe you just do this..

Try this..

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

LOAN = alt(IF(STATUS='ACTIVE', LOAN_AMOUNT)*-1, 0)

NO_OF_INS = alt((RANGESUM(AREAS)/INS_AMT)*-1, 0)

[sunny_talwar]

Although I can this has been answered, but is there a reason you are not using set analysis here?

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

LOAN = Only({<STATUS = {'ACTIVE'}, LOAN_NO {"=RECIEVABLE-RECEIPT_AMT > 0"}>} LOAN_AMOUNT)

NO_OF_INS = I guess this can stay the same (not sure what is going on here

[tamilarasu]

Sunny,    

I agree with all of your point and thought the same when I posted the answer. Believe me, I expected your reply and thought to update my post with set expression. But decided to leave as it is (OP marked the solution as correct).

I am not sure about the last expression and field "INS_AMT". So I have added the "If" Condition, to be on the safer side.

[sunny_talwar]

Did not mean to say that your solution is incorrect, I am sure it works (and in fact in some case this might be the only solution because of data coming from different tables) But I thought why not look for a way to improve it using set analysis if it is possible

[tamilarasu]

No. I knew that my solution woks fine. I thought to change the expression from "If" to Set analysis like you mentioned. 

[#8]

Hi , Tamil,

your expression works but results changed as unexpected. I mean not only negative values and null values removed but also changed the positive values unexpectedly.

Thaks for the try.

Priyantha,

[#9]

Hi, Sunny,

Appreciated your effort but expression you suggested has an error on it . Does not work.

Thks.

Priyantha

[#10]

Dear Winston,

Thaks for help,

your expression works, but results are same as my existing one.

Rgds,

Priyantha.