I want to calculate Policies in Force KPI with the below conditions:
Below is the sample data:
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.
The application is being developed in Qliksense and hence, I have no option for Accumulation to be enabled.
I am using this formula:
But I get the output as 3,4,5 which is incorrect.
Can anyone please guide me with the proper solution?