Peek Funtion

[nguyettran_uct]

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

[vchuprina · Accepted solution]

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

[nguyettran_uct · Accepted solution]

Hi  Vchuprina, 

It's  work, thank you for your support 😀

PREVIEW

[vchuprina]

Welcome!