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.

[jmgross72]

Hi Enan!

I spent some time workshopping this use case.  You should submit this as a weekly challenge! It was fun to work through.  

Not sure if this does exactly what you were expecting, but I hope this helps.  

I first prepped a date column instead of the horizontal versions you shared, then joined the requirements to the footprint activity and removed null values so we only have course days for the duration of each course.  Then I generated rows for each course so there is a unique row for each course/section/day.  (for example, AA11-3) is the January AA1, 3rd section.  

I used the multi-row formula to cascade the start dates, including the start date logic.

Then, some light pivoting to get your cascade and monthly classroom requirements.  

Let me know if you have any questions about my logic.