Calculating a Migration Table by Aggregating Changes in Dimensional Parameters

Thu, 27 Feb 2014 19:13:46 GMT

Hi,

I'm trying to create the following excel chart in Qlikview.

I intend to use it to show how PD Grades(probability of defaults) have migrated this year(or any selected year) relative to the year prior. Using a 15 grade PD scale, and assuming that a particular loan is currently ranked 7 in 2014 and was ranked 5 in 2013, it would be counted in the cell PD Grade 7,PD Migration -2 (higher PD's are bad).

I'm ultmately trying to count the number of loans that have moved over the last 12 month and how far each has moved. I'm looking for a home for each one of my 80,000+ loans somewhere in this chart.

The problem that I'm having(at least I think this is the problem) is that my dimension, PD Grade, is ultimately what I'm trying to compute differences for, at the individual account level. So when I try using the following formula to calculate the difference:

only({$<Year = {$(#=Only(year-1)}>}PD) - only({$<Year = {$(#=Only(Year))}>}PD)

I get differences of zero across the board. If I remove PD as my dimension and use account number, for example, it works fine but then my dimension isn't helpful(I have a list of 80,000+rows vs the 15 that I want).

This may not be the most clear problem but any help is greatly appreciated.

Thanks,

Rich

Re: Calculating a Migration Table by Aggregating Changes in Dimensional Parameters

whiteline — Thu, 27 Feb 2014 19:36:54 GMT

Hi.

I guess you have to use aggr function to calculate the differences for each account number and then use some aggregation to get the value for the whole PD Grade.

topic Calculating a Migration Table by Aggregating Changes in Dimensional Parameters in QlikView

Calculating a Migration Table by Aggregating Changes in Dimensional Parameters

Re: Calculating a Migration Table by Aggregating Changes in Dimensional Parameters