Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Register for Events and Webinars directly from Qlik Community: http://bit.ly/2Vpnenx
Not applicable

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:

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.



1 Reply
Honored Contributor II

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


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.