Plotting data in Alteryx with Logic

[Enan]

Problem Statement: Classroom Requirement Planning Automation

I work as a Data Analyst, and part of my responsibility is to estimate how many classrooms the company will require in the next financial year.
Currently, this process is largely manual and time-consuming, and I am exploring ways to automate it.

Input Files

The process is based on three Excel files:

1. Training Requirement File

This file contains all planned trainings for the upcoming financial year.
It includes:

• Course name and course type

• Number of batches required

• Training constraints and parameters, such as:

  • Allowed start dates

  • No training start on weekends

  • Monthly planning boundaries

This file defines what needs to be scheduled and under what rules.

2. Training Footprint File

This file defines the training duration pattern for each course.

• Each row represents a course

• Each column represents a day

• 1 indicates a training day

• 0 indicates a non-training / off day

This file defines how many days and on which days each course runs.

3. Classroom Plot File (Manual Planning File)

This is where the main manual effort happens.

Currently, I:

• Manually plot each training batch on a daily calendar

• Assign batches based on:

  • Course type

  • Number of batches

  • Constraints from the Training Requirement file

  • Training footprint (on/off days)

• Split each training day into two sessions:

  • AM session: 07:00 – 14:30

  • PM session: 15:00 – 22:30

As batches are plotted, a waterfall-style daily classroom usage is created.

Current Output Logic

1. For each day, I calculate the total classrooms required (AM and PM separately or combined).

2. I then take the maximum daily classroom requirement.

3. From daily values, I derive the maximum classroom requirement per month.

4. This process is repeated for all 12 months of the financial year.

Problem

• The manual plotting of batches is very time-consuming

• Any change in inputs requires repeating the entire process

• The risk of human error is high

Question

Is there a way to automate this entire process, especially:

• The batch plotting logic

• Daily classroom utilization calculation

• Monthly peak classroom requirement calculation

I have attached the three Excel files (Training Requirement, Training Footprint, and Classroom Plot) for reference.