Is there an easy way to manipulate the raw data in Qlik to get to where I want to be?
This is the raw corporate data:
| contract ID | month | rent | interest |
| a123 | Apr-10 | 500 | 25 |
| a123 | May-10 | 500 | 25 |
| a123 | Jun-10 | 500 | 25 |
| a123 | Jul-10 | 500 | 25 |
| a123 | Aug-10 | 500 | 25 |
| a123 | Sep-10 | 500 | 25 |
| a123 | Oct-10 | 500 | 25 |
| a123 | Nov-10 | 500 | 25 |
I need to transform it to this in the data load:
| month | This Month Balance | Next Month Balance |
| Apr-10 | 4200 | 3675 |
| May-10 | 3675 | 3150 |
| Jun-10 | 3150 | 2625 |
| Jul-10 | 2625 | 2100 |
| Aug-10 | 2100 | 1575 |
| Sep-10 | 1575 | 1050 |
| Oct-10 | 1050 | 525 |
| Nov-10 | 525 | 0 |
This month balance is the total of all rent + interest from that month and
the next month balance is the same calculation less the rent and interest in that month.
Any ideas would be much appreciated.
Many thanks,
Phil
