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.

[sunny_talwar]

Using Avinash's expressions, I got this

1)

Sum(Aggr(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()), COLL_CODE, LOAN_NO, NAME))

2)

Sum(Aggr(if(IF(STATUS='ACTIVE', LOAN_AMOUNT)>0,IF(STATUS='ACTIVE', LOAN_AMOUNT),null()), COLL_CODE, LOAN_NO, NAME))

3)

Sum(Aggr(if(RANGESUM(AREAS)/INS_AMT>0,RANGESUM(AREAS)/INS_AMT,null()), COLL_CODE, LOAN_NO, NAME))

or

3)

Sum(Aggr(if(RANGESUM(SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT)))))/INS_AMT>0,RANGESUM(SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT)))))/INS_AMT,null()), COLL_CODE, LOAN_NO, NAME))

[Anil_Babu_Samineni]

Sunny, If possible, can you please share me that Workfile. So that, she can try that file...

[#4]

Grate  !!!

Sunny,

I have tried your proposal (aggr), but in that case subtotals and grand total getting correct solutions. So still null values are spearing on the "AREAS" column and "NO_OF_INS"column.

Thanks lot for the big help.

Priyantha.

[Anonymous]

to give the out put

if(ALT((RANGESUM(AREAS)/INS_AMT)*-1, 0)<=0,'',ALT((RANGESUM(AREAS)/INS_AMT)*-1, 0))

[sunny_talwar]

May be share a screenshot of what you are seeing and highlight the part you would not want to see (using the new expressions I provided)

[#7]

[sunny_talwar · Accepted solution]

Here try these now:

1)

Sum(Aggr(

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

IF(STATUS='ACTIVE', LOAN_AMOUNT) > 0 and

RANGESUM(SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT)))))/INS_AMT > 0,

SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT)))),null()), COLL_CODE, LOAN_NO, NAME))

2)

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

IF(STATUS='ACTIVE', LOAN_AMOUNT) > 0 and

RANGESUM(SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT)))))/INS_AMT > 0,

IF(STATUS='ACTIVE', LOAN_AMOUNT),null()), COLL_CODE, LOAN_NO, NAME))

3)

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

IF(STATUS='ACTIVE', LOAN_AMOUNT) > 0 and

RANGESUM(SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT)))))/INS_AMT > 0,

RANGESUM(SUM(IF(STATUS='ACTIVE', IF(FLAG='A'OR FLAG='R',(RECIEVABLE-RECEIPT_AMT)))))/INS_AMT,null()), COLL_CODE, LOAN_NO, NAME))

[#9]

Dear Sunny,

It's grate work, my problem totally solved,

Thanks you once again.........!!!

Priyantha

[sunny_talwar]

Awesome