7 Replies Latest reply on Jan 5, 2017 1:51 PM by Shinichiro Murakami

    Dynamic capacity utilisation

    jonathan.o'grady.0

      Dear tableau friends,

       

       

      I've a question which has been causing me some trouble and I was hoping somebody out there might be able to offer some guidance please.

       

       

      Please find attached workbook with anonymized data.

       

       

      I manage a number of safe-deposit accounts. Each account has a certain number of boxes available for rent on a daily basis.

       

       

      The data attached references the rental history for each account. It details the number of boxes required, for how many days, commencing on a specific date.

       

       

      I would like to calculate the capacity utilisation at different levels of aggregation.

       

       

      So for example, account 19 has 45 boxes available. In October 2006 it managed to rent a total of 20 boxes for a total of 24 days, or 480 box days. Given 31 days in the month, it had available capacity of 45 X 31 = 1395, giving a capacity utilisation of 34%.

       

       

      However, if I look at just 2 October 2006, account 19 rented six boxes, a capacity utilisation of 13% at the day level.

       

       

      I would like to be able to calculate capacity utilisation at different levels of aggregation, from day to week, to month, to quarter et cetera.

       

       

      I need calculations to remain cognisant of boxes rented previously for days running into this period. Similarly, the available capacity will change depending on whether I'm looking at a day (45 boxes for account 19), or a week (315 boxes for account 19).

       

       

      I've tried various approaches, but things seem to go awry when I add more accounts and different levels of aggregation.

       

       

      If anyone had the time and patience to point me in the right direction I would be very grateful!

       

       

      Best wishes,

       

       

      Jonathan