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

    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