topic Re: Calculating Month on Month Movements and Categorizing in QlikView

Calculating Month on Month Movements and Categorizing

aetingu12 — Fri, 26 Oct 2018 07:15:24 GMT

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

Re: Calculating Month on Month Movements and Categorizing

andrey_krylov — Fri, 26 Oct 2018 08:40:48 GMT

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.

Re: Calculating Month on Month Movements and Categorizing

aetingu12 — Fri, 26 Oct 2018 09:50:44 GMT

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.

Re: Calculating Month on Month Movements and Categorizing

andrey_krylov — Fri, 26 Oct 2018 10:44:39 GMT

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;

Re: Calculating Month on Month Movements and Categorizing

aetingu12 — Fri, 26 Oct 2018 11:09:52 GMT

Epic! Thank you very much!

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