Remove or Modify Field Values

[KKumar92]

Hi All,

I am working on an analysis, where I want to show the outstanding amount based on the invoice value. But the field contains both positive and negative amounts.  I want to remove the record where the invoice number is duplicated and calculate the value of unique invoices. Please refer to the table below, Table 1 is the current table, and Table 2 is what I expect to achieve.

Table 1	Table 2
Invoice No	Amount	Invoice No	Amount
100	$   10,000.00	100	$  10,000.00
101	$   15,000.00	101	$  15,000.00
102	$      5,000.00	104	$    3,500.00
102	$   (5,000.00)	105	$    7,500.00
103	$   10,000.00
103	$ (10,000.00)
104	$      3,500.00
105	$      7,500.00
106	$      3,000.00
106	$   (3,000.00)
Total	$   36,000.00	Total	$  36,000.00

Please advise on how this could be done.

Thanks.

[MayilVahanan]

HI @KKumar92 

If one invoice number contains both positive and negative values, then if use sum() , its becomes zero and not display in the chart while using suppress null values.

Try like this

Dim: Invoice Number

Exp: Sum(Amount)

Else, you want to do in script only.

Table1:
LOAD Invoice No, Amount from sourcetable;

Inner Join
Load * where [# of Invoice] = 1;
LOAD [Invoice No], Count([Invoice No]) as [# of Invoice] Resident Table1
Group by [Invoice No];

Hope it helps

[KKumar92]

Hi Mayil Vahanan,

Thanks for the input, I tried both suggested solutions however it does not generate the expected results.

Thanks,

Kishore.

[GaryGiles · Accepted solution]

Have you tried going into your table properties pane,  turning off "Include zero values in the Add-ons section?  Assuming that you sum(Amount) on Invoice NO.

[KKumar92]

Hi Gary,

Thanks for the suggestion, it worked.