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

    Dynamic capacity utilisation


      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,