topic Re: PLUS MINUS SEPARATE in QlikView

PLUS MINUS SEPARATE

sagaraperera — Tue, 30 Jun 2015 04:30:03 GMT

Dear all

I have a excel file. it is include EXCESS PAID column, i want sum only plus figure and minus figure separately.

please help.(EXCESS PAID column is expression)

sagara

CEN_ACR	PAID	EXCESS PAID
7,000.00	7,000.00	-
6,500.00	6,500.00	-
7,000.00	7,000.00	-
2,700.00	2,000.00	(700.00)
10,200.00	10,200.00	-
23,950.00	15,000.00	(8,950.00)
26,750.00	15,000.00	(11,750.00)
13,000.00	13,000.00	-
2,500.00	2,500.00	-
21,000.00	21,000.00	-
13,000.00	13,000.00	-
10,500.00	10,500.00	-
6,000.00	6,000.00	-
8,000.00	8,000.00	-
5,000.00	5,000.00	-
7,500.00	7,500.00	-
15,000.00	41,650.00	26,650.00
10,200.00	12,350.00	2,150.00
13,700.00	8,000.00	(5,700.00)
23,750.00	23,750.00	-

Re: PLUS MINUS SEPARATE

arulsettu — Tue, 30 Jun 2015 04:34:53 GMT

try like this

for plus figures if(EXCESS PAID>0,sum(EXCESS PAID))

for minus figures if(EXCESS PAID<0,sum(EXCESS PAID))

Re: PLUS MINUS SEPARATE

Siva_Sankar — Tue, 30 Jun 2015 05:23:13 GMT

Sanka,

Find the attachment.

Re: PLUS MINUS SEPARATE

sagaraperera — Tue, 30 Jun 2015 05:35:55 GMT

Dear i try it but do not work it please help

Re: PLUS MINUS SEPARATE

jagan — Tue, 30 Jun 2015 05:37:02 GMT

Hi,

Try like this in script

LOAD

CEN_ACR, PAID, EXCESS_PAID,

If(EXCESS_PAID) > 0, 1, 0) AS SignFlag

FROM DataSource;

Now in frontend use this expressions

For + Values= Sum({<SignFlag={1}>} EXCESS_PAID)

For - Values= Sum({<SignFlag={0}>} EXCESS_PAID)

Hope this helps you.

Regards,

Jagan.

Re: PLUS MINUS SEPARATE

Siva_Sankar — Tue, 30 Jun 2015 05:37:19 GMT

Did you open the attached document?

Re: PLUS MINUS SEPARATE

sagaraperera — Tue, 30 Jun 2015 05:47:00 GMT

attachment not in there

Re: PLUS MINUS SEPARATE

Siva_Sankar — Tue, 30 Jun 2015 05:49:41 GMT

find the attachment !

Re: PLUS MINUS SEPARATE

jagan — Tue, 30 Jun 2015 05:57:23 GMT

Hi,

Did you tried the solution which I provided?

Regards,

jagan.

Re: PLUS MINUS SEPARATE

Tue, 30 Jun 2015 06:07:06 GMT

Hi,

If I am correct, '(' represents the -ve value.

So try the below code:

LOAD

CEN_ACR,

PAID,

EXCESS_PAID,

If(left(EXCESS_PAID,1)='(', 0, 1) as Flag

FROM DataSource;

Expressions should be like

For +ve = Sum({<Flag={1}>} EXCESS_PAID)

For -ve = Sum({<Flag={0}>} EXCESS_PAID)

Thanks,

Garry

Re: PLUS MINUS SEPARATE

Siva_Sankar — Tue, 30 Jun 2015 06:08:20 GMT

Re: PLUS MINUS SEPARATE

qlikviewwizard — Tue, 30 Jun 2015 06:22:51 GMT

Hi,

I followed jagan mohan's advise.

Used Pivot table.

Script:

SumNegative:

load *,AutoNumber([EXCESS PAID]) as Sno;
load * , If(([EXCESS PAID]) > 0, 1, 0) AS SignFlag;
Load *,num(PAID) as PAIDNew;
LOAD CEN_ACR,
    PAID,
    [EXCESS PAID]
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet1);

Re: PLUS MINUS SEPARATE

sagaraperera — Tue, 30 Jun 2015 07:28:27 GMT

Thanks dear Your answer is correct

Re: PLUS MINUS SEPARATE

sagaraperera — Tue, 30 Jun 2015 07:28:33 GMT

Thanks dear Your answer is correct

Re: PLUS MINUS SEPARATE

sagaraperera — Tue, 30 Jun 2015 07:28:44 GMT

Thanks dear Your answer is correct

Re: PLUS MINUS SEPARATE

qlikviewwizard — Tue, 30 Jun 2015 07:30:24 GMT

Hi sagaraperera,

Please select mark as Answered and Helpful. So that we can close the thread.

Thank you.

Re: PLUS MINUS SEPARATE

qlikviewwizard — Tue, 30 Jun 2015 07:36:05 GMT

Hi ANKA PERERA

Please select one reply as the correct answer.

FYI, screen

Re: PLUS MINUS SEPARATE

jagan — Tue, 30 Jun 2015 09:21:05 GMT

Hi,

If you got the Answer please close this thread by giving Correct Answer to the helpful post.

Regards,

jagan.