Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am working on calculating efficiency of class utilization. There are two tables and one go the details of the students who have participated for classes and the other got the respective week's class schedules.
My intention is to calculate the efficiency of the class utilization.
Please fin the attached excel sheet for the details.
I want to calculate the utilization percentage of classes based on the number of students participated for a respective class for a week out of total available capacity for the respective class for the week. Thus, I am looking at the class room utilization on a weekly basis..
I gave a thought of grouping the student's actual participation in to weeks and divide it by the total number of available capacity for the respective week. However, still I do not have a clear idea of implementing the solution.
Appreciate, if someone could help me with this issue. Hope there can be surely a better way of doing this which does not strike in my head at the moment.
Thank you in advance.
Kind regards,
Andy
Hi All,
Thought of adding bit clarity for the above question posted.
The output should be something like below;
I am intending to add some calendar objects so that the user can pick a particular month and the respective week number so that it will show the appropriate results.
Month & Week | Jul-26 | ||
Class | Number of Students participated | Maximum Number of capacity | Utilization Performance |
Maths | 4 | 30 | 13.33% |
Science | 1 | 36 | 2.78% |
Arts | 3 | 24 | 12.50% |
Biology | 4 | 18 | 22.22% |
Appreciate a lot, if someone could help me with this and I am also working on that to come up with a solution.
Thank you in advance.
Kind regards
Andy
Here you go:
I also attach the application that you can work on later.
There is the code in case you do not have licences:
Data:
LOAD Class & [Class Session] as Key,
Class,
[Class Session],
[Number of sessions per week],
[Group Capacity],
[Group Capacity] * [Number of sessions per week] as [Weekly group capacity]
FROM
(ooxml, embedded labels, table is [Class room capacity]);
Students:
LOAD Class & [Class Session] as Key,
Week(Class_Start) as Week,
Student_ID
FROM
(ooxml, embedded labels, table is [Student Attendance])
;
Above you have the Pivot table with expressions so there would be no problem to recreate the app.
Hi Mindaugas,
Thanks a lot fro the reply.
I'll run the code and will let you know the outcome.
Thanks again.
Kind regards,
Andy