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,

IF u want to get the subtotal correct use >o for only in Area

lik 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)))))

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

NO_OF_INS = RANGESUM(AREAS)/INS_AMT

[Anil_Babu_Samineni]

Priyantha, Follow this Null Values --> https://community.qlik.com/thread/85918 Negative Values --> https://community.qlikview.com/thread/115551

[tamilarasu]

Fine.

AREAS: 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(Not Isnull(AREAS), IF(STATUS='ACTIVE', LOAN_AMOUNT))

NO_OF_INS: RANGESUM(AREAS)/INS_AMT

[stephenedberkg]

hi

You can write script itself that is the easiest way for your requirement

script add this

if( AREAS >0, 1, 0) as Flags,

in expression

=sum({<  Flags = {'1'}  >}AREAS)

[sunny_talwar]

Is the issue resolved yet? If it is, please close this thread, if it isn't please let us know what isn't working.

Qlik Community Tip: Marking Replies as Correct or Helpful

[#7]

Dear Sunny,

Expected output is achieved from the expressions introduced above, But as i think there should be one more modification to this. Still i'm trying to find the way.

Thanks.

Priyantha.

[sunny_talwar]

Would you be able to point out what is still missing? I might have missed it in the conversation above

[#9]

Even though your expression removes the negative and null values the impact negative values are available in the grand total.

As i think 'sum' function to be take to the front of the expression instead of 'if'. But i couldn't do the thing successfully.

[sunny_talwar]

I guess try using Sum(Aggr(, dimensions)) to get the grand total here

[sunny_talwar]

Not sure which expression actually worked, but try adding this to the expression which worked:

Sum(Aggr(Expression, COLL_CODE, LOAN_NO, NAME))