1 Reply Latest reply: Feb 27, 2014 2:37 PM by whiteline _

# Calculating a Migration Table by Aggregating Changes in Dimensional Parameters

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

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.