I am creating a Sense application to review Portfolio At Risk for partner lending operations, and I need to be able to display historical rates for a few categories of risk, segmented by lender. I have two goals here:
I could conceivably run this calculation in SQL to return one row per day in the historical record, per partner lender, with the sum of loan value outstanding in each category in the following columns. However, I'd like to try to get this done inside Qlik for a few reasons:
I have the following relevant tables in my data model so far:
[Account State Transitions]:
ended_when (this field is null in the case of the most recent transition)
To effect the charts I want, I need:
So my specific questions are:
Thanks for the quick reply, digvijay!
If I understand the canonical date article, however, this strategy requires that one of the tables in the data model must be "a table where each record only has one value of each date type associated." Unfortunately, in my data, any given Account can have many Payments and many Transitions, Payments have one Account but many relevant Transitions, and Transitions have one Account but many relevant Payments.
Do you have any advice for adapting this strategy in the context of that constraint? Thanks again!
Edit: I don't really see why I shouldn't connect to Accounts; what am I going to break if I build a DateBridge table that has one row per account per relevant date, such that an account with it's own registration_date, 5 effective_date(s), and 5 started_when(s) would appear as 11 total rows on the DateBridge table?
If you can share sample data and expected output, I could try few things, it helps in receiving better and more responses over here.
I've attached a qvf with some sample data so you can see a sample of the load script I have so far. (You'll notice I slightly simplified my original schema description and didn't mention the fact that the amount_usd actually lives on a Receipts table that has a 1:1 relationship with Payments for our purposes)
My thinking now is that I ought to create a single master Event table that lists registrations, payments, and transitions all together with a single date column, construct a "master" calendar against each of the categories of Events and a "canonical" calendar against the entire Events table.
My desired outcome is two-fold:
#1 is a higher priority than #2 for me, so if there's a schema that gets us to #1 but not #2 I'm very much interested.
Update: Here is my new schema:
"Canonical Events" is a concatenation of the three types of events I have in Accounts, Transitions, and Payments, with a Type column. "LOR" is just a two-column table that returns the Likelihood of Recovery for a generic account given a certain number of whole days of default.
I'm putting together a graph to show the total amount outstanding by Distributor and by Account State, but I've run into a wall with trying to Dimension the most recent Transition's "to_state" per account. I'm trying to put together a bar graph with Distributor Name as the first dimension and the current status of all the Distributor's accounts as the second dimension. Looking through a few blog posts, it sounds like FirstSortedValue() and possibly Aggr() are necessary, but I can't seem to get these to work.
First I tried:
On the grounds that I'm looking for the "to_state" of the most recent transition, and the dimensions are already separating things out based on Distributor name. I realize this would probably fail to segment the results by Account, but I can't tell what the result is because I simply get an "Invalid dimension" error on the chart. (Which is a sublimely useless error message)
Then I read more and realized I probably need an Aggr() statement to create the values which FirstSortedValue will evaluate, and I tried this:
=firstsortedvalue(to_state, -aggr(transition_started_date, account_id))
On the grounds that I want to find the most recent to_state across each group of transition_started_dates, grouped by account_id.
I am still getting the "Invalid dimension" error
Any guidance on how I can get this dimension to work would be great, (or a method for accomplishing the same thing using a Measure and Set analysis) and bonus rep to anyone who can figure out a way to do it that allows me to include a variable defining the maximum date that should be considered for the purpose of the evaluation. (Eventually I'll want to be able to restrict this calculation based on a date selected by the user in order to display the measure in question as it would have appeared on the selected day)