Skip to main content

I run a fleet, and we log trips data with relevant info (date, driver and vehicle#).

I’d like a way to count ‘how many days a person/vehicle is active’, in a given date range.


Generally, we would know this with the count function. But in this case, we only get the trips count, and not the active days count.


Within a given date range, I need a formula to calculate



  • how many days a driver was active (has atleast 1 trip/day).

  • how many days a vehicle was active (has atleast 1 trip/day).


Just couldn’t find a solution to this simple requirement, however hard I try to find.

Is there a way to do this? Any ideas are welcome. Cheers

@Ramesh I had to think about it for a bit, but I think I found a solution. I’m assuming you have a trips table that looks something like this:



Note that each driver and van is attached to each trip. Based on the above example table, Bob went on 3 trips on the 12th, and Bob, Sally, and Joe each went on a trip on the 11th. Bob also went on 1 trip on the 13th and 10th as well.


Thus, if you were to select the date range from May 11-May 12, you would want the following information:


Bob: 4

Sally: 1

Joe: 1

Van 1: 2

Van 2: 2

Van 3: 2


Note that overall, May 10th and May 13th should be left out if it works, meaning Van 1 overall is 2 less, and Bob is also 2 less.


The solution here is to use a Rollup instead of a Count. Here is an example:



Drivers



Vans


As you can see, the counts line up. The way this works is by using the toggle to allow only certain conditions to be included. Here is a screenshot for reference:



This should allow you to create fields with specific counts based on certain dates. Now, if you need each day to only show up as active or not active, then you need to change your aggregation formula so that it only selects unique dates. Here is a screenshot for reference:



Now Bob would have a count of 2 instead of 4, because he had trips on the 11th and 12th. It ignores the fact that he had multiple trips on the 12th. See below:



I hope this helps!

@Ramesh I had to think about it for a bit, but I think I found a solution. I’m assuming you have a trips table that looks something like this:



Note that each driver and van is attached to each trip. Based on the above example table, Bob went on 3 trips on the 12th, and Bob, Sally, and Joe each went on a trip on the 11th. Bob also went on 1 trip on the 13th and 10th as well.


Thus, if you were to select the date range from May 11-May 12, you would want the following information:


Bob: 4

Sally: 1

Joe: 1

Van 1: 2

Van 2: 2

Van 3: 2


Note that overall, May 10th and May 13th should be left out if it works, meaning Van 1 overall is 2 less, and Bob is also 2 less.


The solution here is to use a Rollup instead of a Count. Here is an example:



Drivers



Vans


As you can see, the counts line up. The way this works is by using the toggle to allow only certain conditions to be included. Here is a screenshot for reference:



This should allow you to create fields with specific counts based on certain dates. Now, if you need each day to only show up as active or not active, then you need to change your aggregation formula so that it only selects unique dates. Here is a screenshot for reference:



Now Bob would have a count of 2 instead of 4, because he had trips on the 11th and 12th. It ignores the fact that he had multiple trips on the 12th. See below:



I hope this helps!


Wow man. Thanks a ton for the effort.


seems a bit complicated, but lemme try and see if this works 🙂 :crossed_fingers:

Reply


Terms & Conditions