Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
We have a set of people on our system who have a start date and a leave date. Our users want to see who was "live" at any point in the past, based on month and year combinations. They also want the data broken down by month/year.
I can't think of a way to do this, as they could pick (for example) 2010 May, June and October and 2011 January. So the chart would need to show those four months only (not a problem), but also show the people who were live in those months based on their start/end date.
Any ideas?
Many thanks
Nick
Hi Nick.
First you will need to create all possible dates between the lowest date (in both fields - start and leave date) and highiest date (in both of them to);
Example:
TableXXX:
Load
PersonID,
StartDate,
LeaveDate;
SQL Select xxxxxxxxx From yyyyyy;
[MinDate]:
Load Min(StartDate) as MinStartDate
Resident TableXXX;
[MaxDate]:
Load Max(LeaveDate) as MaxLeaveDate
Resident TableXXX;
// YYYY-MM-DD = ISO standard format for dates (most compatible)
Let Var_MinDate = Date(Peek('MinStartDate', 0, 'MinDate'), 'YYYY-MM-DD');
Let Var_MaxDate = Date(Peek('MaxLeaveDate', 0, 'MaxDate'), 'YYYY-MM-DD');
Drop tables MinDate, MaxDate;
//Now you have the highiest and the lowest dates... you will need to fill this interval.
// Example
[DATESBETWEEN]:
Load
'$(Var_MinDate)' + RowNo() as [Dates]
AutoGenerate($(Var_MaxDate) - $(Var_MinDate));
// Now You have a Table DATESBETWEEN With all possible dates. Now you will make a Cartesian product of DATESBETWEEN and TableXXX.
join (TableXXX)
Load Dates
Resident DATESBETWEEN;
Drop Table DATESBETWEE;
//And finally, you wil need to filter your Cartesian Product (on TableXXX) between the StartDate and LeaveDate;
//Example
[TableResult]:
Load * Resident TableXXX Where Dates >= StartDate and Dates <= LeaveDate;
Drop Table TableXXX;
Thats the idea... In the end, the table 'TableResult' will have the time between the start and leave date.
Good Luck,
Bruno
Hi Bruno,
Thanks for the reply. I get the building of a table of all possible dates and joining that to the master table. It's your final bit of the solution that I'm not sure about.
You're explicitly putting in the load all dates >= start date and <= leave date. I can't do this as users can change this in the app to pick any combination of months and even years. So I don't know at the load point what dates are going to be picked!
Yes, thats why we needed to make a cartesian product and, after that, make a filter explicitaly like you said, filling the intervals of start and leave dates.
1) You Had:
TempXXX
People StartDate LeaveDate
John 2010-01-01 2010-01-03
Peter 2010-01-02 2010-01-02
Mary 2010-01-02 2010-01-05
2) We made dates between the min(StartDate) = 2010-01-01 and Max(LeaveDate) = 2010-01-05
Dates
2010-01-01
2010-01-02
2010-01-03
2010-01-04
2010-01-05
3) Then we joinned with the master table...
TempXXX
People StartDate LeaveDate Dates
John 2010-01-01 2010-01-03 2010-01-01
John 2010-01-01 2010-01-03 2010-01-02
John 2010-01-01 2010-01-03 2010-01-03
John 2010-01-01 2010-01-03 2010-01-04
John 2010-01-01 2010-01-03 2010-01-05
Peter 2010-01-02 2010-01-02 2010-01-01
Peter 2010-01-02 2010-01-02 2010-01-02
Peter 2010-01-02 2010-01-02 2010-01-03
Peter 2010-01-02 2010-01-02 2010-01-04
Peter 2010-01-02 2010-01-02 2010-01-05
Mary 2010-01-02 2010-01-05 2010-01-01
Mary 2010-01-02 2010-01-05 2010-01-02
Mary 2010-01-02 2010-01-05 2010-01-03
Mary 2010-01-02 2010-01-05 2010-01-04
Mary 2010-01-02 2010-01-05 2010-01-05
4) Then we explicitly filter the field "Dates" between the StartDate and LeaveDate (Dates >= StartDate and Dates <= LeaveDate)
And we will have:
TempXXX
People StartDate LeaveDate Dates
John 2010-01-01 2010-01-03 2010-01-01
John 2010-01-01 2010-01-03 2010-01-02
John 2010-01-01 2010-01-03 2010-01-03
Peter 2010-01-02 2010-01-02 2010-01-02
Mary 2010-01-02 2010-01-05 2010-01-02
Mary 2010-01-02 2010-01-05 2010-01-03
Mary 2010-01-02 2010-01-05 2010-01-04
Mary 2010-01-02 2010-01-05 2010-01-05
After doing what I suggested, you would use the field "Dates" often StartDate or LeaveDate in the layout...
if someone select 2010-01-02 it will show that John, Mary and Peter was live..
If someone select 2010-01-01 and 2010-01-03 it will show that John, Mary was live.. not Peter because he wont...
Wasn't that the main problem? If not, sorry... I'd understand wrong...
Regards,
Bruno
Nick,
I assume you have a Calendar with dates or Months that the users can select... You could link the Date from the Calendar with the pairs of Start/Leave Dates using INTERVALMATCH (if it doesn't interfere with the rest of your data model). If you can do that, the rest will happen auto-magically 🙂