Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a student data with admission date and leaving date values. I want to show the following visualisation:
For each student in each month how many days they stayed.
Example:
Student id admissionDate leaving Date
S1 1/1/2020 10/2/2020
S2 1/2/2020 14/4/2020
Required data table:
Student id M1 M2 M3
S1. 31. 10. 0
S2. 29. 31. 14
M- month
Thanks it works. How can i make it start from M0?
Sorry try this,
'M'&(AutoNumber(Month(AdmissionDate+IterNo()),StudentID)-1) As Mn
Using this data
TempData:
Load * Inline [
StudentID,AdmissionDate,LeavingDate
S1,1/1/2020,10/2/2020
S2,1/2/2020,14/4/2020
];
Temp:
Load
min(AdmissionDate) as minDate,
max(LeavingDate) as maxDate
Resident TempData;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
Date($(varMinDate) + IterNo() - 1) as CalDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
Inner Join IntervalMatch(CalDate)
Load AdmissionDate
,LeavingDate
Resident TempData;
Calendar:
Load AdmissionDate
,LeavingDate
,CalDate
,If(Month(AdmissionDate) = Month(CalDate),1,Month(CalDate)-Month(AdmissionDate)+1) as Mth
Resident TempCalendar;
Drop Table TempCalendar;
Use the IntervalMatch function to assign calendar dates to the student information. Create a new field Mth, which is the student attendance month (CalDate-AdmissionDate+1)
The measure in the pivot table is just count(Mth)
One solution,
SET DateFormat='D/M/YYYY';
tab1:
Load * Inline [
StudentID,AdmissionDate,LeavingDate
S1,1/1/2020,10/2/2020
S2,1/2/2020,14/4/2020
];
Left Join(tab1)
LOAD *, Date(AdmissionDate+IterNo()) As Dt,'M'&AutoNumber(Month(AdmissionDate+IterNo()),StudentID) As Mn
Resident tab1
While AdmissionDate+IterNo()<=LeavingDate;
Output:
Thanks it works. How can i make it start from M0?
Try this,
'M'&AutoNumber(Month(AdmissionDate+IterNo()),StudentID)-1 As Mn
Sorry try this,
'M'&(AutoNumber(Month(AdmissionDate+IterNo()),StudentID)-1) As Mn