PLUS MINUS SEPARATE

[sagaraperera]

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	-

[arulsettu]

try like this

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

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

[Siva_Sankar]

Sanka,

Find the attachment.

[sagaraperera]

Dear i try it but do not work it please help

[jagan]

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.

[Siva_Sankar]

Did you open the attached document?

[sagaraperera]

attachment not in there

[Siva_Sankar]

find the attachment !

[jagan]

Hi,

Did you tried the solution which I provided?

Regards,

jagan.

[#10]

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