Calendar Post Schedule Tasks
The purpose of this guide is to demonstrate how to use Calendar Post Schedule Tasks to build a template for calculating Equipment Count.
To follow this guide you must have a Calendar Table in your model that is linked to your Scenario.
Equipment Count
A common request is the ability to report the total equipment count per reporting period. The application has an inbuilt field "Equipment Count" that reports this number on a per equipment basis. However, the aggregation of this number means that it cannot be effectively summed in a pivot table or gantt chart.
The steps below, while requiring an initial investment of time, provide a template that can be modified to fit the required logic of your scenario.
Step 1: Set Up Calendar Table Fields
Open the Fields section of your Calendar table and create a section to store the value for each equipment.
Create two fields per equipment. The first will hold the operating hours and the second will be a calculated field to convert the operating hours to an equipment count.
Finally, create another calculated field that sums the equipment count fields.
The flexibility of this method is that each equipment has its own logic that converts from operating hours to equipment count. The logic used in this example is for demonstration purposes only.
Additional fields can be created with logic that provides a Minimum and Maximum equipment count, to provide best and worst case scenarios.
The calculated field expressions used in this example are:
"Standard Count" : Rounds to nearest integer. The denominator applies a concept of minimum operating hours per equipment.
Round(GetValue(PostScheduleTasksEquipmentCAT6040OperatingHours) /(GetValue(Hours) * 0.65),0)
"Minimum Count" : Extends the first formula by adding an equipment if any operating hours are present. Rounds down.
If(GetValue(PostScheduleTasksEquipmentCAT6040OperatingHours) > 0, Max(1, Floor(GetValue(PostScheduleTasksEquipmentCAT6040OperatingHours) / (0.65 * GetValue(Hours)))),0)
"Maximum Count": Extends the first formula by adding an equipment if any operating hours are present. Rounds up.
If(GetValue(PostScheduleTasksEquipmentCAT6040OperatingHours) > 0, Max(1, Ceiling(GetValue(PostScheduleTasksEquipmentCAT6040OperatingHours) / (0.65 * GetValue(Hours)))),0)
Lastly, ensure that the fields you created to sum the individual equipment counts have the correct calculated field expression.
Step 2: Create a Calendar Post Schedule Task
Open your scenario and make sure the Calendar table from Step 1 is linked to the scenario. You can check this in the Settings tab. Next, click on the Post Schedule Tasks tab.
Right click the header and create a New Copy Schedule Results to Calendar Table entry.
Create a row for each piece of equipment. Make sure to select the correct Field and Equipment.
The value expression for each row will be "ScheduleSum(OperatingHours)".
It is recommended that you include a Run Condition so that the Post Schedule Task will automatically update your results. The Run Condition in this example is "RunSourceScheduling". This means that each time the schedule is run with Source Scheduling enabled this Post Schedule Task will update after the schedule has been populated.
Run the schedule to perform the Post Schedule Task, or right click the task and select Run.
Check that the results have been written to the fields in your Calendar table and that your calculated field expressions are providing the correct results.
Step 3: Reports
You can now create custom fields to pull the data from your Calendar table to be used in reports.
Make sure to set the aggregation for these custom fields to Maximum. This ensures that you do not under-report the required equipment count. Do not set the aggregation to None or Sum.
Create a custom field for each of the 'Total' fields on your Calendar Table. Next, create custom fields with the Expression Type of "Per Equipment".
These custom fields can then be used in Pivot Tables and Gantt Charts.
This pivot, called "Monthly Total Equipment", uses the three custom fields reading from the calculated fields summing the total equipment used per month.
"Monthly Per Equipment" uses the three custom fields with the expression type "Per Equipment" to present the equipment count per equipment, per month.
You can also use the custom fields in the Results section of your Gantt Charts to present expected equipment count requirements alongside your key data points.
