Need cumulative distinct count in bar chart

twinklea — Sat, 03 Jun 2017 08:43:36 GMT

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?

Re: Need cumulative distinct count in bar chart

Tyler_Waterfall — Thu, 05 Jul 2018 18:23:42 GMT

I believe you need something different because you are doing count distinct. Perhaps some tips here? Calculating rolling n-period totals, averages or other aggregations

topic Re: Need cumulative distinct count in bar chart in App Development

Need cumulative distinct count in bar chart

Re: Need cumulative distinct count in bar chart