Plotting Values based on Logic within Month

Question

Dear Experts,

Good Day!

Being a beginner in Alteryx i thought of a automating my work and require your guidance and support.

The attached file has a sample table.

Requirement:

1. Its part of my role to plan for yearly how many batches of each course we need based on the forecasted count of student for each course.

2. in order to do it for quite some time i have made a excel file where i manually plot the student headcount of each course type and batch size on different days on the month. Since i cannot plan / start  the batches on certain day.

3. its a tedious task and too much manual work.

4. Logic is i cannot start the batch on weekends and not more than 3 batches for each course type.

5. in some courses i can make exception where there is no option but varies among the batch.

In the attached sample file i have manually plotted some of the courses by each day and course type.

For example:

1.English course on 03-Feb-25 shows 30 (15 x 2) which is count of students and two batch as batch size is 15.

2. In some cases highlighted in Orange based on the forecast i am unable to make a proper batch as per batch size so i reduce the batch size like: Geography 06 Feb 25, 2 batches of 12 (12x2 = 24) and one batch of 08 makes a total of 32.

This i have to do it for all the 12 months and everything when requirement changes i have to redo and its happens very often.

Can you please help me out to resolve this manual plotting in Excel sheet.

In the sample file i have just put Feb (1-15) and March (01-11) but in actual its for 12 months and every month have its own forecasted requirement.

Thank you so much, feel free to ask any questions.

Req (1).xlsx

KGT · Answer

In your provided template, the only relevant info is the first 4 columns, as the rest are calculated. It would not make sense to allocate the classes horizontally, however you can report them that way. (Cross-tab at the end of the workflow).

So, the size/batch is a full class and you have a limit on the number of classes per day. That probably answers all 3 questions. That's an additional condition and can be applied once you have allocated enough full classes to get close to that limit. If you have 4 courses and 10 classrooms, that doesn't apply until you have allocated at least 2 classes per subject. The multiplier field in my workflow would also represent number of classes.

I'm sure you don't only have 4 classes in your dataset (and most likely have a limit on the number of teachers per day), and so rules on how to allocate the leftover will be dependent on your requirements on the full dataset. Possible ideas are:

* Allocate one more course of any leftovers and keep going until all leftovers are finished.
* Allocate the leftover courses 1 at a time until exhausted starting with the course that has the greatest leftovers.
* Work out full classes left, and allocate randomly, then deal with the classes that are not full.
* Group the whole lot into full classes at the start and allocate classes instead of students. (This actually might be better for the whole thing...)

And as for this:

" Secondly i want if the plotting can be done on each random day skipping the weekend or the days define."

Do you mean that you want them randomly assigned? You can see in the workflow, where the calendar is created, that each day is created and then Friday/Saturday is removed.

Enan · Answer

Thank you so much for the guidance and help and apologies for the very late reply. I am extremely sorry.

to answer your question:

1.it depends on the total class rooms avaiable. For example if i have 10 class rooms on Monday i cant exceed 10 courses to be started on that day.

2. I did not get your question.

3. It depends on the anticipated requirement.

I have tried the workflow and want if possible to follow the template which i provided. Secondly i want if the plotting can be done on each random day skipping the weekend or the days define.

i really appreciate your time and guidance

KGT · Answer

I'm not sure on a couple of things here.

* How do you choose how many batches on a day
* How do you choose which days have partial complete batches to match the total?
* In your example, you mention a batch of 8, how is that decided?

You've got to figure out your logic for what this looks like in an ideal situation. I've built a starter workflow that shows some of the logic as to how you would set this up. If you can get it working for one fortnight, then you will be able to expand that to multiple through some different techniques. This is a method I use constantly in Alteryx, by getting it right on a group and then expanding that by writing the rules that account for multiple groups.

In the below screenshot:

* We first set up the calendar and course capacity details and append them together.
* Then allocate any full batches over the period.
* This then leaves the amount that can not fill a full batch each day
* This is where you need to make decisions on what your logic is.

Options from there are:

* Fill from the first day
* Randomly sort the dates and fill daily complete batches from the first row
* Fill every second row until out

Allocating Students.yxmd