0 Replies Latest reply: Jun 7, 2017 11:08 PM by Twinkle Agarwal RSS

    Need cumulative distinct count in bar chart

    Twinkle Agarwal

      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?