Morning all,
Ok so I know that in this situation I need a table calculation. The problem is that I am having a hard time coming up with said the table calculation I need, so I am hoping someone will help me.
Here's the situation:
I have to look at the cost and utilization of my company's fleet vehicles. So each organization within my company (Org 1, Org 2, Org 3) all have three different vehicles and each month there is a cost and accumulated mileage for each vehicle.
Utilization is the biggest concern for me and my project and what I am most interested in.
The test to see whether a vehicle was utilized or not is the monthly accumulated mileage. If the monthly accumulated mileage was less than 400 miles then the vehicle is under utilized.
What I want to compute is the number of vehicles per organization that were under utilized divided by the total number of vehicles for an organization and display this as a percentage.
So if you look at Oct 2018:
Org 1:
Vehicles Under Utilized: 1
Total Vehicles 3
Percentage of vehicles under utilized = 33%
Org 2:
Vehicles Under Utilized: 2
Total Vehicles 3
Percentage of vehicles under utilized = 66%
Org 3:
Vehicles Under Utilized: 1
Total Vehicles 3
Percentage of vehicles under utilized = 33%
Eventually, I would like to expand this to include the some sort of calculation that would expand to look at utilization around a 6 month time window. Meaning that if a vehicle was in the shop because of accident, then it is natural that it was not used. In this project that's not really what we are going for. What we are trying to get to is the efficiently in how a vehicle is utilized, therefore if we said something like - the count of occurrences in the last 6 months that a vehicle was under utilized.....
Additionally, month over month costs are are pretty simple to show on a line graph. Maybe another metric that would be good would be the accumulated month over month cost for a vehicle regardless of the organization.
I am using Tableau 2018.1
Thank you in advance for your help!
Scott Carman
Hello
You can use a fixed calculation for this.
Mileage Test:
{ FIXED [Vehicle ID], DATETRUNC('month', [Date]):
IF SUM([Milage])<400 THEN 1
ELSE 0
END}
Count of Vehicles:
{ FIXED [Org], DATETRUNC('month', [Date]):
countd([Vehicle ID])}
percentage:
SUM([Milage Test])/SUM([count of vehicles])
Hope this helps! see attached.
