Calculating Month on Month Movements and Categorizing

[aetingu12]

Dear all,

I realize that the title is not very descriptive, but here is what I am trying to do:

I have a table:

LOAD * INLINE [

    Reporting Date, Account no, Start_Date, End_Date, Balance, Category, Sub Category, Movement

    31/01/2018, A, 31/01/2016, 31/01/2020, 100, Existing, 0

    31/01/2018, B, 31/01/2016, 28/02/2018, 50, Existing, 0

    28/02/2018, A, 31/01/2016, 31/01/2020, 80, Existing, Decrease, -20

    28/02/2018, B, 31/01/2016, 28/02/2018, 0, Existing, Repaid, -50

    28/02/2018, C, 28/02/2018, 31/01/2020, 10, New, New, 10

    31/03/2018, A, 31/01/2016, 31/01/2020, 70, Existing, Decrease, -10

    31/03/2018, C, 28/02/2018, 31/01/2020, 20, Existing, Increase, 10

];

What I have is; Reporting Date, Account no, Start_Date, End_Date and Balance.

What I want to calculate is Category, Sub Category and Movement.

If Category is New, the Movement should just be the Balance, but if the Category is Existing, then it should be the Movement from the prior date.

Then if the movement is negative then Decrease else increase.

I have tried a few ways to do this, but for some reason, I am having difficulties calculating the month on month movements.

Any help/guidance is appreciated.

Regards,

Aksel

[andrey_krylov]

Hi Aksel. What about doing it in the load script

table1:

LOAD * INLINE [

    Reporting Date, Account no, Start_Date, End_Date, Balance, Category, Sub Category, Movement

    31/01/2018, A, 31/01/2016, 31/01/2020, 100, Existing, 0

    31/01/2018, B, 31/01/2016, 28/02/2018, 50, Existing, 0

    28/02/2018, A, 31/01/2016, 31/01/2020, 80, Existing, Decrease, -20

    28/02/2018, B, 31/01/2016, 28/02/2018, 0, Existing, Repaid, -50

    28/02/2018, C, 28/02/2018, 31/01/2020, 10, New, New, 10

    31/03/2018, A, 31/01/2016, 31/01/2020, 70, Existing, Decrease, -10

    31/03/2018, C, 28/02/2018, 31/01/2020, 20, Existing, Increase, 10

];

table2:

Load [Reporting Date], [Account no], [Start_Date], [End_Date], [Balance], [Category], [Sub Category]

     ,If(Category = 'New', Balance, If(Category = 'Existing', Previous(Movement), If(Movement < 0, 'Decrease', 'increase' ))) as Movement

Resident table1 Order by [Account no], [Reporting Date];

Drop Table table1;

The only thing, I did not get what Decrease increase are.

[aetingu12]

Thank you for this.

The issue is that the Categories, Movements and the sub Category needs to be calculated from the Balances.

First step to calculate the Category:

     -Check if Start Date is equal to the Reporting Date, if so, mark Category as New, else Existing.

Second Step is to calculate the Movement:

     -Calculate Month on Month movement (this is where I have the main issue)

For every account, I would like to create a new column of Movements where I have the difference from the prior month.

So for example for Account A in February you have a balance of 80 and in the following month (March) the balance reduces to 70 so the movement column should be -10.

If this number is negative then it is a decrease if positive then increase.

Hope it makes a bit more sense.

[andrey_krylov · Accepted solution]

Oh, I see. Then maybe like this

table1:

LOAD * INLINE [

    Reporting Date, Account no, Start_Date, End_Date, Balance

    31/01/2018, A, 31/01/2016, 31/01/2020, 100

    31/01/2018, B, 31/01/2016, 28/02/2018, 50

    28/02/2018, A, 31/01/2016, 31/01/2020, 80

    28/02/2018, B, 31/01/2016, 28/02/2018, 0

    28/02/2018, C, 28/02/2018, 31/01/2020, 10

    31/03/2018, A, 31/01/2016, 31/01/2020, 70

    31/03/2018, C, 28/02/2018, 31/01/2020, 20

];

table2: NoConcatenate

Load [Reporting Date], [Account no], [Start_Date], [End_Date], [Balance]

     , if([Start_Date] = [Reporting Date], 'New', 'Existing') as Category

     , if([Account no] <> Previous([Account no]) and [Start_Date] <> [Reporting Date], 0, Balance - Previous(Balance)) as Movement

     , if([Start_Date] = [Reporting Date], 'New',  If(Balance = 0, 'Repaid', if([Account no] = Previous([Account no]), If(RangeSum(Balance, -Previous(Balance)) > 0, 'Increase', 'Decrease'), '') ) ) as [Sub Category]

Resident table1 Order by [Account no], [Reporting Date];

Drop Table table1;

[aetingu12]

Epic! Thank you very much!

I didn't know about the existence of 'Previous'