Determine the last value of an object in the previous year
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.
