Calculating a Migration Table by Aggregating Changes in Dimensional Parameters
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:
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.