Trail Balance trend/movement

Question

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

Customer2024-08-202024-08-192024-08-18xyz100015501550abc-188419988226

day 2

Customer2024-08-212024-08-202024-08-19xyz910510001550abc-18587-18841998

Bren_Spill2 · Answer

@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

TB Data.yxmd