Generate metrics for one vehicle when fleet, make or specific registration is selected

[spikelovesmetal]

Hi,

We've got a fleet of 58 vehicles that we used to manage by registration, so for example we would get individual requests per vehicle for MOTs, servicing, breakdowns, repairs etc. Every entry is tied to an individual vehicle, so there are hundreds of entries but it's fairly straightforward to filter on one vehicle and get all the information we need. We could also search a reference or description and see what vehicles it applied to, i.e. how many vehicles had to have bodywork repairs, or had punctures.

Our current tracker also generates Pivot metrics based on this as well, so you can filter by single or multiple vehicles and it'll tell you what the repair status is, how much it cost, whether it has to go back for more work etc. This comes up in a separate dashboard tab with a chart for each (status, cost, time in repair etc).

The reporting system has now changed so we no longer get individual requests per vehicle. We now have three categories to manage:

• Fleet - every vehicle in the fleet, so for example inspections, servicing and MOTs come into this as a monthly, quarterly or yearly requirement
• Model Specific - for example recalls, or when a set of vehicles reached end of life and are looking to be replaced
• Vehicle Specific - a vehicle has a 'unique' problem, i.e. a breakdown, or someone crumples it on a bollard (again)

I've now split the tracker reference table so that it looks a bit like this. There are a lot more columns to the right on the real one with cost, time, recurrence etc but this is the main bit:

A	B	C	D	E	F	G	H	I	J	K	L
Request Type	Reference	Title	Description	Status	All Vehicles	Ford	Vauxhall	Opel	Honda	Mitsubishi	Vehicle Specific
Fleet Wide	FL-12345	Racking Inspection	Quarter 3 racking inspection	In Progress	Yes (autofills G,H,I,J,K)	Y	Y	Y	Y	Y	All
Model Specific	VA-12345	Warranty - Headlight Alignment	Warranty headlight repair	Complete			Y	Y
Vehicle Specific	IN-12345	Door Repair	O/F door bodywork damage	Pending						Y	DV61SK

The biggest problem I have now is that I will soon have quite a few of these to manage once it rolls over to the new reporting system next month. Using the example above, although I can filter the table manually and (eventually) find out that vehicle DV61SK should have two requests by filtering the columns - one for racking inspection and one to fix the door - I don't know how to update my Pivot to allow me to one-click like I used to. I want to have it so that if I selected DV61SK in the Pivot, I would get:

• All Fleet Wide issues
• All Model Specific issues affecting Mitsubishi
  • Biggest problem currently is I don't know how to 'tell' it that DV61SK is a Mitsubishi
  • We also have some issues which affect multiple models, for example Vauxhall and Opel are essentially the same but with different badges on
• All Vehicle Specific issues only affecting DV61SK

I have a table of registrations and models on a separate tab as a look-up. I also considered in Column L 'Vehicle Specific' just copying a list of the affected registrations in to replicate how it used to be set up, but with 58 vehicles it's time consuming and turns ugly very quickly.

Does anyone know how this can be done?

Thanks!

 

[Fluff]

Cross posted Generate metrics for one vehicle when fleet, make or specific registration is selected

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[spikelovesmetal]

Cross posted Generate metrics for one vehicle when fleet, make or specific registration is selected

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Hello,

This is the original post - I also asked ExcelForum later as I'd appreciate any help I can get. I have provided a note of the link (can't create links on that forum yet) on the ExcelForum post to this one (original post). I was worried because it's a tricky problem that has a deadline. I understand that replies take time on either site but I thought that asking lots of people might help to get an answer in time.

ExcelForum: Generate metrics for one vehicle when fleet, make or specific registration is selected

Sorry for the confusion