Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Need help calculating movement in load script

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Need help calculating movement in load script

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";



talk is cheap, supply exceeds demand

View solution in original post

1 Reply
MVP & Luminary
MVP & Luminary

Re: Need help calculating movement in load script

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";



talk is cheap, supply exceeds demand

View solution in original post