Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a case as below, please give me some advises to correct the formula
Date sample:
Customer code | Date | Amount of Billing | Closing Balance |
092 | 01/01/2022 | 5,000 | 100,000 |
092 | 01/02/2022 | 6,000 | |
092 | 01/03/2022 | 7,000 | |
092 | 01/04/2022 | 8,000 | |
193 | 01/01/2022 | 1,000 | 150,000 |
193 | 01/02/2022 | 2,000 | |
193 | 01/03/2022 | 3,000 | |
193 | 01/04/2022 | 4,000 |
Desired reults
Customer code | Date | Amount of Billing | Closing Balance |
092 | 01/01/2022 | 5,000 | 100,000 |
092 | 01/02/2022 | 6,000 | 94,000 |
092 | 01/03/2022 | 7,000 | 87,000 |
092 | 01/04/2022 | 8,000 | 79,000 |
193 | 01/01/2022 | 1,000 | 150,000 |
193 | 01/02/2022 | 2,000 | 148,000 |
193 | 01/03/2022 | 3,000 | 145,000 |
193 | 01/04/2022 | 4,000 | 141,000 |
I group and order data by Customer Code and Date, the formula is:
If(IsNull("Closing Balance") ,Peek("Closing Balance") - "Amount of Billing", "Closing Balance") as "Closing Balance"
The problem is that the results is not separate by Customer code, it only use the "Closing Balance" of the first Customer and combine "Amount of Billing" of all customer with the same Date to calculate
Hi,
Try the following code:
DATA_TMP:
LOAD * Inline[
Customer code, Date, Amount of Billing, Closing Balance
092, 01/01/2022, 5000, 100000
092, 01/02/2022, 6000,
092, 01/03/2022, 7000,
092, 01/04/2022, 8000,
193, 01/01/2022, 1000, 150000
193, 01/02/2022, 2000,
193, 01/03/2022, 3000,
193, 01/04/2022, 4000,
];
DATA:
NoConcatenate
LOAD
[Customer code],
Date,
[Amount of Billing],
IF([Customer code] = Previous([Customer code]), Peek("Closing Balance")-[Amount of Billing], [Closing Balance]) AS [Closing Balance]
Resident DATA_TMP
Order By [Customer code], Date;
DROP Table DATA_TMP;
My result:
Regards,
Vitalii
Hi,
Try the following code:
DATA_TMP:
LOAD * Inline[
Customer code, Date, Amount of Billing, Closing Balance
092, 01/01/2022, 5000, 100000
092, 01/02/2022, 6000,
092, 01/03/2022, 7000,
092, 01/04/2022, 8000,
193, 01/01/2022, 1000, 150000
193, 01/02/2022, 2000,
193, 01/03/2022, 3000,
193, 01/04/2022, 4000,
];
DATA:
NoConcatenate
LOAD
[Customer code],
Date,
[Amount of Billing],
IF([Customer code] = Previous([Customer code]), Peek("Closing Balance")-[Amount of Billing], [Closing Balance]) AS [Closing Balance]
Resident DATA_TMP
Order By [Customer code], Date;
DROP Table DATA_TMP;
My result:
Regards,
Vitalii
Welcome!