Forum Discussion

KimRavizza's avatar
KimRavizza
Qrew Cadet
3 years ago

Scheduling Capacity Chart

Hi All,

I have a Stores table that contains all the data for my stores, including the Delivery/Install date for the equipment we're installing.

I'm trying to create a stacked bar chart that shows the number of installs we can schedule in a week vs the number we have actually scheduled (to show how much contingency we have)

So for example, in week 1 we could schedule a total of 16 stores. We have scheduled 12, so we have room to schedule 4 more. 

I think I need the following fields:
Week Number (1 to 23 - based on the first day of the week of the Delivery/Install Date)
Weekly Capacity - varies by week number, as some weeks have holidays, so less capacity
# of Stores Scheduled - how many stores have a Delivery/Install Date in a given week

The Delivery/Install Date and Week Number fields are in the Stores table. To get the # of Stores Scheduled, I'm assuming I would need to create a summary field in a parent table

I've tried creating a "Capacities" table, with the week numbers and the capacity for each week. Set up a relationship with the Stores table where the Capacities is the parent and the Stores is the child. I'm just drawing a blank on how to generate the related Stores.

Am I over-complicating this? Just drawing a blank this morning, so any help anyone can provide would be very much appreciated.​

I'm sure I've probably left out some key info, so please feel free to ask questions to clarify.

Thanks in advance!​​​

------------------------------
Kim
------------------------------
Formulas and functions
reports and charts

1 Reply

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    I think you are on the right track. Set up a table of weekly capacities where the key field of the table is a date field and the date is the first Sunday of the week. You can do this in excel and import the next 10 years of Sundays.  

    Then on your installation schedule record you can make a formula for the first day of the week  
    FirstDayOfWeek(Install date])

    Take a relationship where one weekly capacity has many installations and use that first day of week field as the reference field in your installations child table.  

    No I cannot think of a way to get the first to look exactly like you have them but you could have a formula field that shows the percent of full capacity which is the number of installations divided by the capacity expressed as a percent. Then the bars could indicate how close they are below or above 100%.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------