1 Reply Latest reply: Jul 5, 2018 2:23 PM by Tyler Waterfall

# Need cumulative distinct count in bar chart

Hi,

I want to calculate Policies in Force KPI with the below conditions:

1. Calendar Date>=Effective Date and <=EndDate for all  policies and for each dates throughout
2. It has to be a cumulative view in the bar chart
3. 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.

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?