Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
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.
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)
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
Hi Priyantha,
Check the attachment.
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)
Dear Joshua,
Your expression works as expected but i cant get the correct subtotal.
Thkx for the attempt.
Priyantha.
Hi, Avinash,
Your expression works as expected but i cant get the correct subtotal.
Thkx for the attempt.
Priyantha.
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
Dear Tamil,
I have only PE version, please post on the wall,
Thnx,
Priyantha.