Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)