Hello Community,
We struggle to find an algorithm for our specific problem. For illustrating purposes, let’s consider the following table:
| Technical Object Key | Transaction date | To be paid |
| ABC123V1 | 15-11-2014 | 200 |
| ABC123V2 | 15-12-2014 | 100 |
| ABC123V3 | 15-1-2015 | 0 |
| ABC123V4 | 15-2-2015 | 1200 |
| DEF123V1 | 15-1-2013 | 500 |
| DEF123V2 | 1-8-2014 | 800 |
| DEF123V3 | 1-4-2015 | 700 |
*In the table above, the Object Key is containing two parts: The insured object of a client (e.g. ABC123) and the specific version of that insured object (e.g. V1)
In our project we would like to determine the sum of the last ‘to be paid’ value of an insured object of the previous year (here we need to ignore the version of the insured object). Key here, is that the last to be paid value of the previous year should only be determined for those technical object keys which are part of user based time selection.
See below for some use cases and their desired outcome:
Use case 1.
User makes the following time selection in QlikView: 1-1-2015 up to and including 1-6-2015
Result of ‘to be paid’, for the insured object ABC123: 100 (the transaction date of 15-12-2014)
Result of ‘to be paid’, for the insured object DEF123: 800 (the transaction date of 1-8-2014)
The total result of the algorithm therefore must be: 900
Use case 2.
User makes the following time selection in QlikView: 1-5-2015 up to and including 1-12-2015
The total result of the algorithm therefore must be: 0 (because none of the transaction dates occurred in the given QlikView time interval)
Use case 3.
User makes the following time selection in QlikView: 1-7-2014 up to and including 31-12-2014
Result of ‘to be paid’, for the insured object ABC123: 0 (There are no transaction in the previous year for the insured object)
Result of ‘to be paid’, for the insured object DEF123: 500 (the transaction date of 15-1-2013)
The total result of the algorithm therefore must be: 500
Hope you can help in pointing us in the right direction here.
Kind regards,
Arjan IJlenhave
