topic Re: How to remove negative and null values from expression in QlikView

How to remove negative and null values from expression

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: How to remove negative and null values from expression

ajsjoshua — Thu, 28 Apr 2016 05:09:07 GMT

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.

Re: How to remove negative and null values from expression

sujeetsingh — Thu, 28 Apr 2016 05:26:29 GMT

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)

Re: How to remove negative and null values from expression

avinashelite — Thu, 28 Apr 2016 05:26:59 GMT

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

Re: How to remove negative and null values from expression

tamilarasu — Thu, 28 Apr 2016 05:30:56 GMT

Hi Priyantha,

Check the attachment.

Re: How to remove negative and null values from expression

sasikanth — Thu, 28 Apr 2016 05:39:01 GMT

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)

Re: How to remove negative and null values from expression

Thu, 28 Apr 2016 06:05:08 GMT

Dear Joshua,

Your expression works as expected but i cant get the correct subtotal.

Thkx for the attempt.

Priyantha.

Re: How to remove negative and null values from expression

Thu, 28 Apr 2016 06:06:38 GMT

Hi, Avinash,

Your expression works as expected but i cant get the correct subtotal.

Thkx for the attempt.

Priyantha.

Re: How to remove negative and null values from expression

perumal_41 — Thu, 28 Apr 2016 06:07:05 GMT

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

Re: How to remove negative and null values from expression

Thu, 28 Apr 2016 06:08:01 GMT

Dear Tamil,

I have only PE version, please post on the wall,

Thnx,

Priyantha.

Re: How to remove negative and null values from expression

ajsjoshua — Thu, 28 Apr 2016 06:13:44 GMT

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

Re: How to remove negative and null values from expression

Anil_Babu_Samineni — Thu, 28 Apr 2016 06:36:20 GMT

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

Re: How to remove negative and null values from expression

tamilarasu — Thu, 28 Apr 2016 06:39:16 GMT

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

Re: How to remove negative and null values from expression

stephenedberkg — Thu, 28 Apr 2016 07:30:44 GMT

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)

Re: How to remove negative and null values from expression

sunny_talwar — Thu, 28 Apr 2016 10:00:23 GMT

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

Re: How to remove negative and null values from expression

Thu, 28 Apr 2016 10:48:43 GMT

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.

Re: How to remove negative and null values from expression

sunny_talwar — Thu, 28 Apr 2016 10:50:59 GMT

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

Re: How to remove negative and null values from expression

Thu, 28 Apr 2016 11:07:53 GMT

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.

Re: How to remove negative and null values from expression

sunny_talwar — Thu, 28 Apr 2016 11:11:27 GMT

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

Re: How to remove negative and null values from expression

sunny_talwar — Thu, 28 Apr 2016 11:12:33 GMT

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

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