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.
