Is there a way to determine what changed between two summaries?

[#1]

In other words, on one date, the total of loans is 1,000 and the next it's 2,500. Is there a way to determine the details of what changed in between: balance changes, paid off loans, new loans?  I'd like a list of the loans that changed and what the change was.

[#2]

Julia,

Welcome to the Community!

In order for the Community members to help you, you probably need to provide a bit more information about your use case, like your data structures, etc.

[eduardo_dimperio]

Hi Julia,

I didn't quite understand what you want.

You have two tables and want to know the difference between both? Can you give some example of input and output please.

Thank You

[#4]

I have a summary of loan balances for 2 different dates.  As an example, the total in loans on 7/31 is $1,000; the total in loans on 8/31 is $2,000.  Management wants to be able to see the details of what caused the difference in these 2 totals.

• What loans had balances at 7/31 but are now paid off so don't contribute to the total for 8/31?
• What loans had payments between the 2 dates so had different balances on each date?
• What new loans have been booked since 7/31 so won't be in the 7/31 total but will be in the 8/31 total?

Basically I need to provide a list of additions, deletions, and changes that impacted the totals on the 2 dates so that they can understand why the total balances changed.  I hope that makes a little more sense!

[eduardo_dimperio]

Yeah a little bit, but imagine that to show to your management what's happen to your loans we need all information that affect it like to build a table and formulas, like in a Excel. And in you example you just give one date, a input value and a output value.

Ex:

Input:

Number of Loan, Date, Payment, Credit, Etc.

Understand?

[rwunderlich]

It's pretty simple...but as others have requested, we need some sample of what your data looks like.

-Rob

[#7]

Julie,

If you only have summary of loans on specific dates, and not transaction detail (payoffs, etc.) you won't be able to create reports that you describing. You might want to get the detail information first (if possible) and describe its structure (fields, data types, etc.) here.

[#8]

This is my first post, so clearly I have a lot to learn on how to explain what I’m asking.  Thanks for your patience!

Imagine a file such as this:

Date	Acct#	Balance	Status
7/31/2017	1	100	Open
7/31/2017	2	150	Open
7/31/2017	3	200	Open
7/31/2017	4	300	Open
7/31/2017	5	250	Open
8/31/2017	1	0	Paid Off
8/31/2017	2	150	Open
8/31/2017	3	175	Open
8/31/2017	4	350	Open
8/31/2017	5	250	Open
8/31/2017	6	500	Open
8/31/2017	7	400	Open
8/31/2017	8	175	Open

The summary report would show the total balance as of 7/31 as 1,000 and as of 8/31 as 2,000.  I’d like to then be able to show what specifically changed.  It could be presented in several different ways, but the basic information needed would be:

Acct#	Balance Change	Type
1	-100	Paid Off
2	0	No Change
3	-25	Balance Reduction
4	50	Balance Increase
5	0	No Change
6	500	New Acct
7	400	New Acct
8	175	New Acct

My experience is with reporting tools such as Cognos where this kind of comparison between dates is pretty straightforward.  Is it achievable in Qlik Sense?

[eduardo_dimperio]

Nice, with this we can work.

If i understand your need:

Aux:

Load

Acct,

Balance,

Status,

Date

If(Acct=Peek(Acct),Balance -Peek(Balance)) AS Balance_Change

FROM

YOURQVD

Order by Acct;

Final:

Load

*,

If(Balance_Change=0,'No Change'

If(Balance_Change=-100,'Paid Off'

If(Balance_Change=-25,'Balance Reduction'

If(Balance_Change=50,'Balance Increase'

If(Balance_Change=500,'New Acct'

If(Balance_Change=400,'New Acct'

If(Balance_Change=175,'New Acct'

)

Resident Aux

Drop Table Aux;

[rwunderlich]

Attached is a sample QVF.  I may have taken too simple of an approach.

Raw:

LOAD

Month("Date") as Month,

    "Date",

    Acct#,

    Balance,

    Status

FROM [lib://Thread]

(html, codepage is 28591, embedded labels, table is @1);

Summaries:

LOAD *,

if(Status='Paid Off', 'Paid Off'

,if([Balance Change]='New', 'New Acct'

    ,if([Balance Change]=0, 'No Change'

    ,if([Balance Change]>0, 'Balance Increase'

    ,if([Balance Change]<0, 'Balance Decrease'

    ,'????'

    ))))) as Type

;

LOAD

*,

    if(Acct# = Previous([Acct#]), Balance - Previous(Balance), 'New')

    as [Balance Change]

Resident Raw

Order by [Acct#], "Date"

;

DROP TABLE Raw;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com