Need help calculating movement in load script

[#1]

Hi everyone

I have the following table layout:

SQL SELECT
Account_Balance, (This is the life to date balance of this account at the end of a specific Accounting_Period)

Account_Code, (This is your GL Account Code)                        
Accounting_Period, (This is the Accounting_Period)                   
Cost_Centre (This is the cost centre / department)                   
FROM "DataWhse".dbo."Balance_Sheet_Balances";

In a nutshell, what I need is to calculate the movement amount for a specific accounting period.

I need to have a life to date balance of every single account by cost centre by accounting period as well as

a movement amount by account by cost centre for every single accounting period.

I think I have a solution, but I have no idea how to write the script for it. If this is not the correct solution, please provide me with your idea.

I would like to sum the Account_Balance for every accounting period where Accounting Period = Accounting Period - 1. I would then call this account balance, Account_Balance_Previous_Period. By having this, I can now deduct the newly created Account_Balance_Previous_Period from the initial Account_Balance. This should leave me with the movement between the balance of the current month and the balance of the prior month.

I would appreciate the help.

Cheers

[Gysbert_Wassenaar · Accepted solution]

Perhaps like this:

LOAD *,
     If(Cost_Centre = previous(Cost_Centre), rangesum(Account_Balance, -previous(Account_Balance)) as Movement;
SQL SELECT *
     Account_Balance, 
     Account_Code,                
     Accounting_Period,      
     Cost_Centre              
FROM "DataWhse".dbo."Balance_Sheet_Balances";