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,

Jonathan

• ###### 1. Re: Dynamic capacity utilisation

HI Jonathan

Here is my approach with using parameters.

[Month]

datetrunc('month',[Start date])

[Week]  // You can change the start date of week

datetrunc('week',[Start date],'Monday')

if [Parameter 1]= "Week" then date([Week] )

elseif [Parameter 1]= "Month" then date([Month])

else[Start date]

end

[Capacity]

if [Parameter 1]="Week" then min([Boxes available])*7

elseif [Parameter 1]="Month" then min([Month days])*min([Boxes available])

else min([Boxes available]) end

[Boxes Used]

sum([Boxes])*sum([Days])

[Utilizaion %]

[Boxes Used]/[Capacity]

Thanks,

Shin

• ###### 2. Re: Dynamic capacity utilisation

Shin,

Thank you kindly for considering my little conundrum!

I think your answer is almost there, but if I am understanding correctly, it fails to consider previously rented boxes into the next period.

If a customer rents three boxes today for four days, and another customer rents two boxes tomorrow for three days, then we are utilising four boxes today and six boxes for the next three days.

Maybe I have misunderstood your proposed solution? If so my apologies!

Thanks again for your help. Let me know if you have any more ideas!

Best wishes,

Jonathan

• ###### 3. Re: Dynamic capacity utilisation

I need to understand the correct logic to calculate "used" boxes from [Boxes] and [Days] (/and also date?).

Thanks,

Shin

• ###### 4. Re: Dynamic capacity utilisation

Of course, no problem!

Boxes used is the total number rented

Date is the time when the renting begins

So if I use three boxes for two days on 4 January, then I am using three boxes on 4 January and three boxes on 5 January

Best wishes,

Jonathan

• ###### 5. Re: Dynamic capacity utilisation

That's quite tricky to calculate in Tableau as "first impression".

Probably possible, but feeling it's quite troublesome task.

Are you going to calculate it in Tableau or is there any other method to calculate it outside of Tableau?

So if I use three boxes for two days on 4 January, then I am using three boxes on 4 January and three boxes on 5 January

Thanks,

Shin

PS.

I meant we need individual date's score, but your data does not have "all" the dates as dimensions.

• ###### 6. Re: Dynamic capacity utilisation

Shrin,

I think you're right. I've wasted quite enough time on this problem. I think I will revert to Python.

It's a shame, conceptually it's very easy to understand. I'd be surprised if I'm the 1st to uncover this issue.

Thank you for giving it your consideration.

Best wishes,

Jonathan

• ###### 7. Re: Dynamic capacity utilisation

Tableau is not a calculate tool primary,so if there is no data, it's sometimes to CREATE data newly.

I think we can try something, but considering the total workload, sometimes it's more reasonable to prepare data for tableau outside.

Thanks,

Shin