Register
Submit a solution
The challenge is finished.

Challenge Overview

Project Background
Our customer has an existing reporting system for their various types of reports, however the  current system has a lot of limitations and also has performance issues. They started using Qlik and found it easy to work with, so the plan is to migrate all their reports to be Qlik based.
 
For this project, we are migrating 6 of the reports (5 of them belong to the same big report actually) to Qlik using Qlik Sense. Here are the 6 reports in scope of the whole project:
  1. Americas Lev Lending Limits Utilization Dashboard
  2. Credit Risk Review Dashboard which contains 5 different tabs / reports:   
    1. Credit Risk Review Report
    2. Top 10 Industry PE
    3. Top 10 Geographic PE
    4. Top 10 Collateral Types Notional
    5. Top 10 Client PE
 
For development purposes you may have to use Qlik Sense Desktop on windows, but you can also use Qlik Sense Cloud the online version if you can implement all our requirements.
 
There will be a series of challenges for this project so get started on this new tool early to stay ahead.
 
Challenge Objectives
In the previous challenge, we have built the Credit Risk Review Report but it’s missing an important feature: data aggregation at different levels. In this challenge, we are going to update the report to include the missing feature.
 
Please read the sections below to find out more details about the challenge scope.
 
Technology Stack
  • Qlik Sense Desktop or Cloud
  • Excel
 
Challenge Scope
For this whole project, we’ll only be building UI related features, nothing about backend or api integration is expected, and that’s of course the case for this challenge as well.
 
For this challenge, you are expected to update the existing Credit Risk Review Report as stated below. You’ll be working with data that matches the exact structure of data used in the reports, but with dummy values. We’ll provide this data to you in the forum.

Challenge Requirements

  • Please check the FSD for detailed spec about the Credit Risk Review Dashboard. And the requirement we need to implement in this challenge is FR03_01, we need to add the dropdown to the right middle panel and make sure when different values are selected from the dropdown, the table gets updated with data aggregated at the selected level.
  • The report should solely use the Portfolio-CounterPartyDetails sheet as data source, right now the report also uses the Portfolio sheet which is wrong, the data currently shown in the Portfolio should all be calculated from the Portfolio-CounterPartyDetails sheet and that’s exactly what we need to do in this challenge.
  • Note CoB is an input dropdown field, we should use that to filter data based on the 'Last Review Date' column. The values of CoB should come from some kind of config (file). We can have 13 values in this config as stated in FSD and later the customer can replace this with an API call to get the list of CoB. Each CoB would be the last day of a month.
  • In general, data aggregation works like this:
    • Portfolio: simply apply the calculation to all rows
    • Legal Entity: group rows by legal entity, then apply calculation to each each group of rows
    • Renewable Entity: group by renewable entity, then apply calculation to each each group of rows
    • Ultimate Parent: group by ultimate parent, then apply calculation to each each group of rows
  • Here are the calculation rules for each column:
    • Total Portfolio PE = sum(Dle Exposure of current month)
    • Total Previous Portfolio PE = sum(Dle Exposure of previous month)
    • Total PE Variance = Total Portfolio PE - Total Previous Portfolio PE
    • Total # of Clients in Portfolio = unique number of counterparty IDs of current month
    • Total  # of Previous Clients in Portfolio = unique number of counterparty IDs of previous month
    • Total Current Portfolio RWA = sum(RWA of current month)
    • Total Previous Portfolio RWA = sum (RWA of previous month)
    • Total Portfolio Collateral Notional = sum (Collateral  of current month)
    • Total PE of New Clients Approved in current month  = sum(Dle Exposure  of current month where New Client = 'Yes')
    • Total PE of New Clients Approved in prior month = sum(Dle Exposure  of previous month where New Client = 'Yes')
    • Total New Clients Approved in current month = unique number of counterparty IDs of current month for new clients
    • Total New Clients Approved in prior month = unique number of counterparty IDs of previous month for new clients
    • Total Watch List PE = sum(Dle Exposure of current month where Non Performance Flag = 'W')
    • Total Surveillance PE = sum(Dle Exposure of current month where Non Performance Flag = 'I')
    • Total Red Flag PE = sum(Dle Exposure of current month where Red Flag = 'Yes')
    • Total NIG PE  = sum(Dle Exposure of current month where Credit Rating ID is lower than BBB - i.e. not blank or not any of 'AAA', 'AA+', 'AA', 'AA-', 'A+', 'A', 'A-', 'BBB+', 'BBB' or 'BBB-' )
    • Total Current Adverse PE = sum(Dle Exposure of current month for rows where any of the value of Special Mention Percent, Substandard Percent, Doubtful Percent, Loss Percent is not zero )
    • Total Previous Adverse PE = sum(Dle Exposure of previous month for rows where any of the value of Special Mention Percent, Substandard Percent, Doubtful Percent, Loss Percent is not zero )
    • Total # of Current Adverse Clients = unique number of counterparty IDs of current month where any of the value of Special Mention Percent, Substandard Percent, Doubtful Percent, Loss Percent is not zero
    • Total # of Previous Adverse Clients = unique number of counterparty IDs of previous month where any of the value of Special Mention Percent, Substandard Percent, Doubtful Percent, Loss Percent is not zero
    • Total Current Impaired PE = sum(Dle Exposure of current month where CSFB Rating ID = 'C' or 'D')
    • Total Previous Impaired PE = sum(Dle Exposure of previous month where CSFB Rating ID = 'C' or 'D')
    • Total # of Current Impaired Clients = unique number of counterparty IDs of current month where CSFB Rating ID = 'C' or 'D'
    • Total # of Previous Impaired Clients = unique number of counterparty IDs of previous month where CSFB Rating ID = 'C' or 'D'
  • Keep the rest of the features working and don’t break anything
  • You should include exported report templates based on your own Qlik app setup
  • You should include a detailed readme (with step-to-step guides) on how to replicate the same Qlik app using the dummy data, so in case we want to set it up from scratch we can easily do it.
  • Make sure your Qlik app can be properly tested, for example: if we update some data in the data files, the Qlik app will still work. This basically means your Qlik app needs to work with any generic data that follows the same data structure, so a solution hardcoded to adapt to a specific set of data is not acceptable.
 
Browser Requirements
This reports should work on all mainstream desktop browsers:
  1. Windows: IE 11+, Chrome Latest, Firefox Latest
  2. Mac: Safari Latest, Chrome Latest, Firefox Latest
 
Documentation
Register to download the following documents we provided in the forum
  • The FSD
  • UI Mockups
  • The dummy data


Final Submission Guidelines

  • Exported Qlik template file(s)
  • Detailed readme with step-to-step guide on how to replicate the same Qlik app using the data we provided, and how to test it

ELIGIBLE EVENTS:

Topcoder Open 2019

Review style

Final Review

Community Review Board

Approval

User Sign-Off

ID: 30070666