10 Replies Latest reply: Sep 30, 2017 2:44 AM by Antonio Mancini

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

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.

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

Julia,

Welcome to the Community!

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

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

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

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!

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

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?

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

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

-Rob

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

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.

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

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?

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

Nice, with this we can work.

Aux:

Acct,

Balance,

Status,

Date

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

FROM

YOURQVD

Order by Acct;

Final:

*,

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;

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

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

Raw:

Month("Date") as Month,

"Date",

Acct#,

Balance,

Status

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

Summaries:

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

;

*,

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

as [Balance Change]

Resident Raw

Order by [Acct#], "Date"

;

DROP TABLE Raw;

-Rob

http://qlikviewcookbook.com

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

Hi Julia,

may be like this

Table:
"Date",
Acct#,
Balance,
Status
FROM [lib://276335]
(html, codepage is 28591, embedded labels, table is @1);