Trail Balance trend/movement

[WassimAB]

Hi Community.

I have a trial balance table that gets updated daily with the previous day’s date. I need to find a way to track the movement of each customer. We'll label the columns as T-1, T-2, T-3, etc., where, for example, T-1 represents the difference between August 21st and August 20th, and T-2 represents the difference between August 20th and August 19th.

Next, I need to calculate the movement between T-1 and T-2, which we'll call TM1, and the movement between T-2 and T-3, which we'll call TM2.

Initially, I considered replacing the date in the table, but I realized this wouldn’t work because a new date gets added the next day.

below data input

day 1

Customer	2024-08-20	2024-08-19	2024-08-18
xyz	1000	1550	1550
abc	-1884	1998	8226

day 2

Customer	2024-08-21	2024-08-20	2024-08-19
xyz	9105	1000	1550
abc	-18587	-1884	1998

[Bren_Spill2]

@WassimAB - I'm not sure how you want your output to look, but one approach is to transpose the data for all incoming days and union it with the previous data so you have a clean table consisting of Customer, Date, and Amount. Remove duplicate records, and use a multi-row tool to calculate the movement per customer. You can then cross tab the data back into the original format if needed - having one table for the TB data and one for the movement.

You would also need to consider how to handle the data coming in daily - maybe a batch macro would work well but not sure without understanding the source of the data.

I have attached a sample - please review the configuration to make sure you understand and let me know if you have any questions.

Thanks