Hello Community,
In our dashboard we present transactional data on insurance based premiums. See below for a very simple example:
Transaction Date | Transaction Key | Transaction amount |
12-2-2015 | 123ABC200B1C2 | €100 |
If we make a time selection in which the transaction date of 12-2-2015 is present, our dashboard is presenting this premium transaction amount nicely. In case the time selection made by an end user is not capturing the transaction date, the dashboard is not showing the transaction above (which is OK).
Now, let’s consider some additional elements on the same transactions:
Transaction Date | Transaction Key | Transaction amount | Accounting start date | Accounting end date | Earned premium till date |
12-2-2015 | 123ABC200B1C2 | €100 | 20-2-2015 | 20-8-2015 | Calculated field based on the maximum date in a user based time selection. Example: MaxDate = 20-4-2015 Earned premium on MaxDate = 100 * ((number of days between 20-4-2015 and 20-2-2015) / (number of days between 20-2-2015 and 20-8-2015)) = 32,5966851 |
Our dashboard should behave as follows:
Time selection | Transaction Amount | Earned premium till date |
1-1-2015 up and including 11-2-2015 | 0 | 0 |
1-1-2015 up and including 18-2-2015 | 100 | 0 |
1-1-2015 up and including 20-4-2015 | 100 | 32,5966851 |
1-3-2015 up and including 20-4-2015 | 0 | 32,5966851 |
21-8-2015 up and including 31-12-2015 | 0 | 0 |
Please help us in finding an algorithm for displaying the Earned premium in the scenario as presented in the table above. We have the feeling that set analysis might help us here, but we are not quite sure how to correctly apply.
Kind regards,
Arjan IJlenhave
