# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for
Did you mean:
Contributor II

## Creating new table from existing data

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:

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

3 Solutions

Accepted Solutions
Contributor II
Author

Thanks it works. How can i make it start from M0?

Sorry try this,

``'M'&(AutoNumber(Month(AdmissionDate+IterNo()),StudentID)-1) As Mn``
6 Replies
Specialist III

Using this data

TempData:
S1,1/1/2020,10/2/2020
S2,1/2/2020,14/4/2020
];

Temp:
max(LeavingDate) as maxDate

Resident TempData;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:
Date(\$(varMinDate) + IterNo() - 1) as CalDate
AutoGenerate 1 While \$(varMinDate) + IterNo() -1 <= \$(varMaxDate);

Inner Join IntervalMatch(CalDate)

,LeavingDate
Resident TempData;

Calendar:
,LeavingDate
,CalDate
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:
S1,1/1/2020,10/2/2020
S2,1/2/2020,14/4/2020
];

Left Join(tab1)
Resident tab1

Output:

Contributor II
Author

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``
Tags
Community Browser