How to capture this transactional data in our QlikView dashboard
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.
