Counting based on date criteria

[RichaU24]

I am trying to generate distinct counts of policies that are active at a particular point in time.

My data looks Like this

Policy Number	Start Date
p1	01-04-2013
P1	01-08-2013
p2	30-08-2013
p2	01-04-2014
p3	30-06-2014
P2	01-08-2014
P1	07-04-2015
P3	30-06-2015
P2	01-08-2015
P1	07-04-2016
p5	30-06-2016

A policy remains active for 1 year from the start date unless a different start date comes up before a full year is complete.

I want to summarize this in the following way. 

Period	Number of Policies active
Feb-13	0
May-15	3 (p1,p2,p3)
Aug-16	2 (p1,p5)

Can somebody pls suggest the way to go about it? 

Thanks

[vchuprina]

Hi,

You should use concat function as in my example below:

DATA_TMP:
LOAD
[Policy Number],
[Start Date],
Date(Date#(Right([Start Date],7), 'MM-YYYY'), 'MMM-YYYY') AS [Month Year]
;
LOAD * Inline[
Policy Number, Start Date
p1, 01-04-2013
P1, 01-08-2013
p2, 30-08-2013
p2, 01-04-2014
p3, 30-06-2014
P2, 01-08-2014
P1, 07-04-2015
P3, 30-06-2015
P2, 01-08-2015
P1, 07-04-2016
p5, 30-06-2016
];

DATA:
LOAD
Count(DISTINCT[Policy Number])&' ('& Concat(DISTINCT [Policy Number], ', ')&')' AS [Number of Policies active],
[Month Year]
Resident DATA_TMP
Group By [Month Year];

DROP Table DATA_TMP;

Regards,

Vitalii

[vinieme12]

or you could just do it in the chart directly,

this is more flexible to do in front end as selections could change possible values

=count(distinct [Policy Number]) & '('& concat(distinct [Policy Number],',') &')'