Count of IDs based on a scenario on the monthyear selection.

[#1]

Hi,

I have a typical scenario where I need to calculate the dis IDs count based on below condition while selecting the calender monthyear.

The following table contain 2 Unique Id data . 0,1,2 in the last position shows the revaluation of ID and update their status.

    This is the sample data.

ID	Start Date	End Date	Status	Rating
aa-123-234-0	12/03/2013	13/09/2013	Enrolled	B
aa-123-234-1	20/10/2014	17/03/2015	Fit	C
aa-123-234-2	19/10/2015	18/04/2016	Fit	D
aa-123-234-3	04/08/2016	29/01/2017	Removed	Failed
bb-345-456-0	07/03/2012	08/04/2013	Enrolled	A
bb-345-456-1	10/10/2014	12/09/2015	Fit	C
bb-345-456-2	18/10/2016	12/02/2017	Fit	B

MOnthyear is a part of common calender & based on end date  & Start date which starts from mar-2012, apr-2012...............till jan-2017.

Condition: When I select feb-2017 in the monthyear then It shouldn't count the aa-123-234 ID as It has been removed on 29/01/2017 but If I select any monthyear previous to that then It should take the aa-123-234 id count in the final output.

Any help will be really appreciated.

final output-- on feb-2017 the final count will be 1.

[sunny_talwar]

May be using IntervalMatch

Table:

LOAD ID,

    [Start Date],

    [End Date],

    Status,

    Rating,

    Left(ID, Index(ID, '-', -1)-1) as SubID

FROM

[..\..\..\Downloads\sample (1).xlsx]

(ooxml, embedded labels, table is Sheet1);

Calendar:

LOAD Date(MinDate + IterNo() - 1) as Date,

  Date(MonthStart(MinDate + IterNo() - 1), 'MMM-YYYY') as MonthName

While MinDate + IterNo() - 1 <= Today();

LOAD Min([Start Date]) as MinDate

Resident Table;

IntervalMatch:

IntervalMatch(Date)

LOAD [Start Date],

    [End Date]

Resident Table;

[#3]

Hi Sunny,

Thank you so much for replying so fast.. I was not doing well for last 5 days that's why couldn't reply on time. This is not exactly the output I am looking for.... The  count should be based on 'End date' not on the start date. Suppose ID aa-123-234-0 should be count as 1 from sept-2013 not from mar-2013. It will be count as 1 until its status become 'removed' . So, for aa-123-234-0 ID the count will be zero from feb-2017.

I hope you understand my requirement.  Thank you once again .  

[sunny_talwar]

May be this

Table:

LOAD ID,

    [Start Date],

    [End Date],

    Status,

    Rating,

    Left(ID, Index(ID, '-', -1)-1) as SubID

FROM

[..\..\..\Downloads\sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD SubID,

  FirstValue([End Date]) as Start,

  LastValue([End Date]) as End

Resident Table

Group By SubID

Order By ID;

Calendar:

LOAD Date(MinDate + IterNo() - 1) as Date,

  Date(MonthStart(MinDate + IterNo() - 1), 'MMM-YYYY') as MonthName

While MinDate + IterNo() - 1 <= Today();

LOAD Min(Start) as MinDate

Resident FinalTable;

IntervalMatch:

IntervalMatch(Date)

LOAD Start,

    End

Resident FinalTable;