Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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.
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!
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?
It's pretty simple...but as others have requested, we need some sample of what your data looks like.
-Rob
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.
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?
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;
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