Hi,
I want to calculate Policies in Force KPI with the below conditions:
- Calendar Date>=Effective Date and <=EndDate for all policies and for each dates throughout
- It has to be a cumulative view in the bar chart
- Policies count should be distinct throughout
Below is the sample data:
CalendarDate | PolicyNumber | TransactionEffective Date | TransactionEnd Date |
08/02/2015 | A1 | 08/02/2015 | 08/02/2015 |
08/02/2015 | A2 | 08/02/2015 | 08/02/2016 |
08/02/2015 | A3 | 08/02/2014 | 08/02/2016 |
09/02/2015 | A4 | 08/02/2015 | 31/12/2017 |
10/02/2015 | A2 | 10/01/2015 | 31/12/2017 |
10/02/2015 | A5 | 07/02/2015 | 31/12/9999 |
Below is the output:
Chart Type: Bar chart
X axis : Calendar Date
Y axis: count(distinct PolicyNumber) -> with the above mentioned conditions
Result should be:
For 08/02/2015 -> cumulative count=3 (It fulfills all the above conditions)
For 09/02/2015->cumulative count =3 (A4 policy is counted here, but the condition for A1 policy that Calendar Date <= EndDate is not fulfilled for the date 09/02/2015 i.e. 08/02/2015 <09/02/2015 )
For 10/02/2015-> cumulative count =4((as A2 policy was also available for previous date, hence not counted again, only A5 policy is considered and added to the previous 3 policies)
Please let me know the solution to get the above output.
Please note:
The application is being developed in Qliksense and hence, I have no option for Accumulation to be enabled.
I am using this formula:
=RangeSum(Above(sum(aggr(count({<CalendarDate={">=$(=DATE(min(TransactionEffectiveDate),'DD/MM/YYYY'))<=$(=DATE(max(TransactionEndDate),'DD/MM/YYYY'))"}>}distinct PolicyNumber),PolicyNumber)),0,RowNo()))
But I get the output as 3,4,5 which is incorrect.
Can anyone please guide me with the proper solution?
