Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
twinklea
New Contributor

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.

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?

1 Reply
Employee
Employee

Re: Need cumulative distinct count in bar chart

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

Community Browser