Hi all,
I have the following simplified data in a database and would like to create a matrix in a dashboard showing for each start date and closing date the movement within this timeframe.
see below a few lines of data with for a few houses (=collateral id) the (energy) label letter and the carrying amount of the related loan. In the column cumulative redemption the redemption/outflow is shown.
A few collateral has been shown in the first table for a certain timeframe. In the final dashboard I would like to select with a filter any startdate and any enddate. Based on these dates I would like to see for each energy label letter the total carrying amount per label (specified by collateral ID) as of the startdate and the gross carrying amount per label as of the enddate/closing date.
If a certain collateral was migrated from one energy label to another energy label the carrying amount of that collateral should be shown under transition out the label it is leaving and under transition in the new energy label it was then is allocated.
Does one of you have suggestions for this dashboard?
Thanks for your advice!
| date | label | collateral id | Carrying amount | Cumulative Redemption | Production | Remark | |
| 31-01-2020 | B | 10101 | 100 | | | | |
| 31-01-2020 | C | 10102 | 200 | | | | |
| 31-01-2020 | D | 10103 | 150 | | | | |
| 28-02-2020 | B | 10101 | 90 | 10 | | | |
| 28-02-2020 | B | 10102 | 195 | 5 | | transition from C to B in feb (carrying amount 195 then) | |
| 28-02-2020 | D | 10103 | 150 | 0 | | | |
| ..... | | | | | | | |
| 30-04-2022 | A | 10101 | 60 | 40 | | transiton from B to A dec 2021 (carrying amount then 75) | |
| 30-04-2022 | A | 10102 | 170 | 30 | | transition from B to A in feb (carrying amount 180 then) | |
| 30-04-2022 | D | 10103 | 0 | 150 | | Sale of the house | |
| 30-04-2022 | F | 10104 | 140 | 0 | 140 | New loand | |
| | | | | | | | |
| | | | | | | | |
| Dashboard | | | | | | | |
| Label | Start 31/01 | Production inflow | Outflow | Transition in | Transition out | Closing period 30/4/2022 | Remark |
| A | 0 | | -25 | 255 | | 230 | Outflow of 25 is redemption on pand id 10101 of 15 (=75-60) and on pand id 10102 of 10 (=180-170) |
| B | 100 | | -40 | 195 | -255 | 0 | Outflow of 40 is redemption on pand id 10101 of 25 (=100-75)and on pand id 10102 of 15 (=195-180) |
| C | 200 | | -5 | | -195 | 0 | |
| D | 150 | | -150 | | | 0 | Outflow of 150 is redemption on pand id 10103 when sold in april 2022 |
| E | 0 | | | | | 0 | |
| F | 0 | 140 | | | | 140 | |
| G | 0 | | | | | | |
| | | | | | | | |
| Total | 450 | 140 | -220 | 450 | -450 | 370 | |
