Sum only not duplicated values

[KarlaB]

Hi!

I have some "projected expenditures" with an ID ($), some of them have been purchased so they have same $ (but -$) and same ID.

Now i want to sum the budget of the pending expenses (without the duplicated ID's, cause they have already been done).

For example, i want Qlik to show me done USD as sum(USD from duplicated id)[Red ones] and pending USD as sum(USD from not duplicated id) [White ones]

Note: I wouldn't like to base on duplicated amount USD, cause we can have some purchases with the same cost, so they would not be displayed, that's why i want to base on ID

[QFabian]

Hi @KarlaB , please check this example.

//Script

Data:
Load * INLINE [
Account, USD, ID
Projected, 125, 123000
Projected, 948, 123001
Projected, 283, 123002
Projected, 9485, 123003
Projected, 28, 123004
Projected, 938, 123005
Expenses, -283, 123002
Expenses, -28, 123004
];

Data2:
Load
Account,
ID,
if(ID = previous(ID), 'Done', 'Pending') as State,
USD
Resident Data
Order by
ID,
USD desc;

drop table Data;
exit script;

Done : Sum({<State= {'Done'}>} USD)

Pending : Sum({<Account= {'Projected'}>} USD) + Sum({<State= {'Done'}>} USD)