Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Calculating Month on Month Movements and Categorizing

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

1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

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;

View solution in original post

4 Replies
Highlighted
Specialist
Specialist

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.

Highlighted
Creator
Creator

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.


Highlighted
Specialist
Specialist

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;

View solution in original post

Highlighted
Creator
Creator

Epic! Thank you very much!

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