Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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 .
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;